"Fossies" - the Fresh Open Source Software Archive 
Member "phpMyAdmin-5.1.0-english/libraries/classes/Plugins/Export/ExportSql.php" (24 Feb 2021, 105440 Bytes) of package /linux/www/phpMyAdmin-5.1.0-english.zip:
1 <?php
2 /**
3 * Set of functions used to build SQL dumps of tables
4 */
5
6 declare(strict_types=1);
7
8 namespace PhpMyAdmin\Plugins\Export;
9
10 use PhpMyAdmin\Charsets;
11 use PhpMyAdmin\DatabaseInterface;
12 use PhpMyAdmin\Plugins\ExportPlugin;
13 use PhpMyAdmin\Properties\Options\Groups\OptionsPropertyMainGroup;
14 use PhpMyAdmin\Properties\Options\Groups\OptionsPropertyRootGroup;
15 use PhpMyAdmin\Properties\Options\Groups\OptionsPropertySubgroup;
16 use PhpMyAdmin\Properties\Options\Items\BoolPropertyItem;
17 use PhpMyAdmin\Properties\Options\Items\MessageOnlyPropertyItem;
18 use PhpMyAdmin\Properties\Options\Items\NumberPropertyItem;
19 use PhpMyAdmin\Properties\Options\Items\RadioPropertyItem;
20 use PhpMyAdmin\Properties\Options\Items\SelectPropertyItem;
21 use PhpMyAdmin\Properties\Options\Items\TextPropertyItem;
22 use PhpMyAdmin\Properties\Plugins\ExportPluginProperties;
23 use PhpMyAdmin\SqlParser\Components\CreateDefinition;
24 use PhpMyAdmin\SqlParser\Context;
25 use PhpMyAdmin\SqlParser\Parser;
26 use PhpMyAdmin\SqlParser\Statements\CreateStatement;
27 use PhpMyAdmin\SqlParser\Token;
28 use PhpMyAdmin\Util;
29 use const E_USER_ERROR;
30 use const PHP_VERSION;
31 use function bin2hex;
32 use function count;
33 use function defined;
34 use function explode;
35 use function implode;
36 use function in_array;
37 use function intval;
38 use function is_array;
39 use function mb_strlen;
40 use function mb_strpos;
41 use function mb_substr;
42 use function preg_quote;
43 use function preg_replace;
44 use function preg_split;
45 use function sprintf;
46 use function str_repeat;
47 use function str_replace;
48 use function stripos;
49 use function strtotime;
50 use function strtoupper;
51 use function trigger_error;
52
53 /**
54 * Handles the export for the SQL class
55 */
56 class ExportSql extends ExportPlugin
57 {
58 /**
59 * Whether charset header was sent.
60 *
61 * @var bool
62 */
63 private $sentCharset = false;
64
65 public function __construct()
66 {
67 parent::__construct();
68 $this->setProperties();
69
70 // Avoids undefined variables, use NULL so isset() returns false
71 if (isset($GLOBALS['sql_backquotes'])) {
72 return;
73 }
74
75 $GLOBALS['sql_backquotes'] = null;
76 }
77
78 /**
79 * Sets the export SQL properties
80 *
81 * @return void
82 */
83 protected function setProperties()
84 {
85 global $plugin_param, $dbi;
86
87 $hide_sql = false;
88 $hide_structure = false;
89 if ($plugin_param['export_type'] === 'table'
90 && ! $plugin_param['single_table']
91 ) {
92 $hide_structure = true;
93 $hide_sql = true;
94 }
95
96 // In case we have `raw_query` parameter set,
97 // we initialize SQL option
98 if (isset($_REQUEST['raw_query'])) {
99 $hide_structure = false;
100 $hide_sql = false;
101 }
102
103 if ($hide_sql) {
104 return;
105 }
106
107 $exportPluginProperties = new ExportPluginProperties();
108 $exportPluginProperties->setText('SQL');
109 $exportPluginProperties->setExtension('sql');
110 $exportPluginProperties->setMimeType('text/x-sql');
111 $exportPluginProperties->setOptionsText(__('Options'));
112
113 // create the root group that will be the options field for
114 // $exportPluginProperties
115 // this will be shown as "Format specific options"
116 $exportSpecificOptions = new OptionsPropertyRootGroup(
117 'Format Specific Options'
118 );
119
120 // general options main group
121 $generalOptions = new OptionsPropertyMainGroup('general_opts');
122
123 // comments
124 $subgroup = new OptionsPropertySubgroup('include_comments');
125 $leaf = new BoolPropertyItem(
126 'include_comments',
127 __(
128 'Display comments <i>(includes info such as export'
129 . ' timestamp, PHP version, and server version)</i>'
130 )
131 );
132 $subgroup->setSubgroupHeader($leaf);
133
134 $leaf = new TextPropertyItem(
135 'header_comment',
136 __('Additional custom header comment (\n splits lines):')
137 );
138 $subgroup->addProperty($leaf);
139 $leaf = new BoolPropertyItem(
140 'dates',
141 __(
142 'Include a timestamp of when databases were created, last'
143 . ' updated, and last checked'
144 )
145 );
146 $subgroup->addProperty($leaf);
147 if (! empty($GLOBALS['cfgRelation']['relation'])) {
148 $leaf = new BoolPropertyItem(
149 'relation',
150 __('Display foreign key relationships')
151 );
152 $subgroup->addProperty($leaf);
153 }
154 if (! empty($GLOBALS['cfgRelation']['mimework'])) {
155 $leaf = new BoolPropertyItem(
156 'mime',
157 __('Display media types')
158 );
159 $subgroup->addProperty($leaf);
160 }
161 $generalOptions->addProperty($subgroup);
162
163 // enclose in a transaction
164 $leaf = new BoolPropertyItem(
165 'use_transaction',
166 __('Enclose export in a transaction')
167 );
168 $leaf->setDoc(
169 [
170 'programs',
171 'mysqldump',
172 'option_mysqldump_single-transaction',
173 ]
174 );
175 $generalOptions->addProperty($leaf);
176
177 // disable foreign key checks
178 $leaf = new BoolPropertyItem(
179 'disable_fk',
180 __('Disable foreign key checks')
181 );
182 $leaf->setDoc(
183 [
184 'manual_MySQL_Database_Administration',
185 'server-system-variables',
186 'sysvar_foreign_key_checks',
187 ]
188 );
189 $generalOptions->addProperty($leaf);
190
191 // export views as tables
192 $leaf = new BoolPropertyItem(
193 'views_as_tables',
194 __('Export views as tables')
195 );
196 $generalOptions->addProperty($leaf);
197
198 // export metadata
199 $leaf = new BoolPropertyItem(
200 'metadata',
201 __('Export metadata')
202 );
203 $generalOptions->addProperty($leaf);
204
205 // compatibility maximization
206 $compats = $dbi->getCompatibilities();
207 if (count($compats) > 0) {
208 $values = [];
209 foreach ($compats as $val) {
210 $values[$val] = $val;
211 }
212
213 $leaf = new SelectPropertyItem(
214 'compatibility',
215 __(
216 'Database system or older MySQL server to maximize output'
217 . ' compatibility with:'
218 )
219 );
220 $leaf->setValues($values);
221 $leaf->setDoc(
222 [
223 'manual_MySQL_Database_Administration',
224 'Server_SQL_mode',
225 ]
226 );
227 $generalOptions->addProperty($leaf);
228
229 unset($values);
230 }
231
232 // what to dump (structure/data/both)
233 $subgroup = new OptionsPropertySubgroup(
234 'dump_table',
235 __('Dump table')
236 );
237 $leaf = new RadioPropertyItem('structure_or_data');
238 $leaf->setValues(
239 [
240 'structure' => __('structure'),
241 'data' => __('data'),
242 'structure_and_data' => __('structure and data'),
243 ]
244 );
245 $subgroup->setSubgroupHeader($leaf);
246 $generalOptions->addProperty($subgroup);
247
248 // add the main group to the root group
249 $exportSpecificOptions->addProperty($generalOptions);
250
251 // structure options main group
252 if (! $hide_structure) {
253 $structureOptions = new OptionsPropertyMainGroup(
254 'structure',
255 __('Object creation options')
256 );
257 $structureOptions->setForce('data');
258
259 // begin SQL Statements
260 $subgroup = new OptionsPropertySubgroup();
261 $leaf = new MessageOnlyPropertyItem(
262 'add_statements',
263 __('Add statements:')
264 );
265 $subgroup->setSubgroupHeader($leaf);
266
267 // server export options
268 if ($plugin_param['export_type'] === 'server') {
269 $leaf = new BoolPropertyItem(
270 'drop_database',
271 sprintf(__('Add %s statement'), '<code>DROP DATABASE IF EXISTS</code>')
272 );
273 $subgroup->addProperty($leaf);
274 }
275
276 if ($plugin_param['export_type'] === 'database') {
277 $create_clause = '<code>CREATE DATABASE / USE</code>';
278 $leaf = new BoolPropertyItem(
279 'create_database',
280 sprintf(__('Add %s statement'), $create_clause)
281 );
282 $subgroup->addProperty($leaf);
283 }
284
285 if ($plugin_param['export_type'] === 'table') {
286 $drop_clause = $dbi->getTable(
287 $GLOBALS['db'],
288 $GLOBALS['table']
289 )->isView()
290 ? '<code>DROP VIEW</code>'
291 : '<code>DROP TABLE</code>';
292 } else {
293 $drop_clause = '<code>DROP TABLE / VIEW / PROCEDURE'
294 . ' / FUNCTION / EVENT</code>';
295 }
296
297 $drop_clause .= '<code> / TRIGGER</code>';
298
299 $leaf = new BoolPropertyItem(
300 'drop_table',
301 sprintf(__('Add %s statement'), $drop_clause)
302 );
303 $subgroup->addProperty($leaf);
304
305 $subgroup_create_table = new OptionsPropertySubgroup();
306
307 // Add table structure option
308 $leaf = new BoolPropertyItem(
309 'create_table',
310 sprintf(__('Add %s statement'), '<code>CREATE TABLE</code>')
311 );
312 $subgroup_create_table->setSubgroupHeader($leaf);
313
314 $leaf = new BoolPropertyItem(
315 'if_not_exists',
316 '<code>IF NOT EXISTS</code> ' . __(
317 '(less efficient as indexes will be generated during table '
318 . 'creation)'
319 )
320 );
321 $subgroup_create_table->addProperty($leaf);
322
323 $leaf = new BoolPropertyItem(
324 'auto_increment',
325 sprintf(__('%s value'), '<code>AUTO_INCREMENT</code>')
326 );
327 $subgroup_create_table->addProperty($leaf);
328
329 $subgroup->addProperty($subgroup_create_table);
330
331 // Add view option
332 $subgroup_create_view = new OptionsPropertySubgroup();
333 $leaf = new BoolPropertyItem(
334 'create_view',
335 sprintf(__('Add %s statement'), '<code>CREATE VIEW</code>')
336 );
337 $subgroup_create_view->setSubgroupHeader($leaf);
338
339 $leaf = new BoolPropertyItem(
340 'simple_view_export',
341 /* l10n: Allow simplifying exported view syntax to only "CREATE VIEW" */
342 __('Use simple view export')
343 );
344 $subgroup_create_view->addProperty($leaf);
345
346 $leaf = new BoolPropertyItem(
347 'view_current_user',
348 __('Exclude definition of current user')
349 );
350 $subgroup_create_view->addProperty($leaf);
351
352 $leaf = new BoolPropertyItem(
353 'or_replace_view',
354 sprintf(__('%s view'), '<code>OR REPLACE</code>')
355 );
356 $subgroup_create_view->addProperty($leaf);
357
358 $subgroup->addProperty($subgroup_create_view);
359
360 $leaf = new BoolPropertyItem(
361 'procedure_function',
362 sprintf(
363 __('Add %s statement'),
364 '<code>CREATE PROCEDURE / FUNCTION / EVENT</code>'
365 )
366 );
367 $subgroup->addProperty($leaf);
368
369 // Add triggers option
370 $leaf = new BoolPropertyItem(
371 'create_trigger',
372 sprintf(__('Add %s statement'), '<code>CREATE TRIGGER</code>')
373 );
374 $subgroup->addProperty($leaf);
375
376 $structureOptions->addProperty($subgroup);
377
378 $leaf = new BoolPropertyItem(
379 'backquotes',
380 __(
381 'Enclose table and column names with backquotes '
382 . '<i>(Protects column and table names formed with'
383 . ' special characters or keywords)</i>'
384 )
385 );
386
387 $structureOptions->addProperty($leaf);
388
389 // add the main group to the root group
390 $exportSpecificOptions->addProperty($structureOptions);
391 }
392
393 // begin Data options
394 $dataOptions = new OptionsPropertyMainGroup(
395 'data',
396 __('Data creation options')
397 );
398 $dataOptions->setForce('structure');
399 $leaf = new BoolPropertyItem(
400 'truncate',
401 __('Truncate table before insert')
402 );
403 $dataOptions->addProperty($leaf);
404
405 // begin SQL Statements
406 $subgroup = new OptionsPropertySubgroup();
407 $leaf = new MessageOnlyPropertyItem(
408 __('Instead of <code>INSERT</code> statements, use:')
409 );
410 $subgroup->setSubgroupHeader($leaf);
411
412 $leaf = new BoolPropertyItem(
413 'delayed',
414 __('<code>INSERT DELAYED</code> statements')
415 );
416 $leaf->setDoc(
417 [
418 'manual_MySQL_Database_Administration',
419 'insert_delayed',
420 ]
421 );
422 $subgroup->addProperty($leaf);
423
424 $leaf = new BoolPropertyItem(
425 'ignore',
426 __('<code>INSERT IGNORE</code> statements')
427 );
428 $leaf->setDoc(
429 [
430 'manual_MySQL_Database_Administration',
431 'insert',
432 ]
433 );
434 $subgroup->addProperty($leaf);
435 $dataOptions->addProperty($subgroup);
436
437 // Function to use when dumping dat
438 $leaf = new SelectPropertyItem(
439 'type',
440 __('Function to use when dumping data:')
441 );
442 $leaf->setValues(
443 [
444 'INSERT' => 'INSERT',
445 'UPDATE' => 'UPDATE',
446 'REPLACE' => 'REPLACE',
447 ]
448 );
449 $dataOptions->addProperty($leaf);
450
451 /* Syntax to use when inserting data */
452 $subgroup = new OptionsPropertySubgroup();
453 $leaf = new MessageOnlyPropertyItem(
454 null,
455 __('Syntax to use when inserting data:')
456 );
457 $subgroup->setSubgroupHeader($leaf);
458 $leaf = new RadioPropertyItem(
459 'insert_syntax',
460 __('<code>INSERT IGNORE</code> statements')
461 );
462 $leaf->setValues(
463 [
464 'complete' => __(
465 'include column names in every <code>INSERT</code> statement'
466 . ' <br> Example: <code>INSERT INTO'
467 . ' tbl_name (col_A,col_B,col_C) VALUES (1,2,3)</code>'
468 ),
469 'extended' => __(
470 'insert multiple rows in every <code>INSERT</code> statement'
471 . '<br> Example: <code>INSERT INTO'
472 . ' tbl_name VALUES (1,2,3), (4,5,6), (7,8,9)</code>'
473 ),
474 'both' => __(
475 'both of the above<br> Example:'
476 . ' <code>INSERT INTO tbl_name (col_A,col_B,col_C) VALUES'
477 . ' (1,2,3), (4,5,6), (7,8,9)</code>'
478 ),
479 'none' => __(
480 'neither of the above<br> Example:'
481 . ' <code>INSERT INTO tbl_name VALUES (1,2,3)</code>'
482 ),
483 ]
484 );
485 $subgroup->addProperty($leaf);
486 $dataOptions->addProperty($subgroup);
487
488 // Max length of query
489 $leaf = new NumberPropertyItem(
490 'max_query_size',
491 __('Maximal length of created query')
492 );
493 $dataOptions->addProperty($leaf);
494
495 // Dump binary columns in hexadecimal
496 $leaf = new BoolPropertyItem(
497 'hex_for_binary',
498 __(
499 'Dump binary columns in hexadecimal notation'
500 . ' <i>(for example, "abc" becomes 0x616263)</i>'
501 )
502 );
503 $dataOptions->addProperty($leaf);
504
505 // Dump time in UTC
506 $leaf = new BoolPropertyItem(
507 'utc_time',
508 __(
509 'Dump TIMESTAMP columns in UTC <i>(enables TIMESTAMP columns'
510 . ' to be dumped and reloaded between servers in different'
511 . ' time zones)</i>'
512 )
513 );
514 $dataOptions->addProperty($leaf);
515
516 // add the main group to the root group
517 $exportSpecificOptions->addProperty($dataOptions);
518
519 // set the options for the export plugin property item
520 $exportPluginProperties->setOptions($exportSpecificOptions);
521 $this->properties = $exportPluginProperties;
522 }
523
524 /**
525 * Generates SQL for routines export
526 *
527 * @param string $db Database
528 * @param array $aliases Aliases of db/table/columns
529 * @param string $type Type of exported routine
530 * @param string $name Verbose name of exported routine
531 * @param array $routines List of routines to export
532 * @param string $delimiter Delimiter to use in SQL
533 *
534 * @return string SQL query
535 */
536 protected function exportRoutineSQL(
537 $db,
538 array $aliases,
539 $type,
540 $name,
541 array $routines,
542 $delimiter
543 ) {
544 global $crlf, $dbi;
545
546 $text = $this->exportComment()
547 . $this->exportComment($name)
548 . $this->exportComment();
549
550 $used_alias = false;
551 $proc_query = '';
552
553 foreach ($routines as $routine) {
554 if (! empty($GLOBALS['sql_drop_table'])) {
555 $proc_query .= 'DROP ' . $type . ' IF EXISTS '
556 . Util::backquote($routine)
557 . $delimiter . $crlf;
558 }
559 $create_query = $this->replaceWithAliases(
560 $dbi->getDefinition($db, $type, $routine),
561 $aliases,
562 $db,
563 '',
564 $flag
565 );
566 // One warning per database
567 if ($flag) {
568 $used_alias = true;
569 }
570 $proc_query .= $create_query . $delimiter . $crlf . $crlf;
571 }
572 if ($used_alias) {
573 $text .= $this->exportComment(
574 __('It appears your database uses routines;')
575 )
576 . $this->exportComment(
577 __('alias export may not work reliably in all cases.')
578 )
579 . $this->exportComment();
580 }
581 $text .= $proc_query;
582
583 return $text;
584 }
585
586 /**
587 * Exports routines (procedures and functions)
588 *
589 * @param string $db Database
590 * @param array $aliases Aliases of db/table/columns
591 *
592 * @return bool Whether it succeeded
593 */
594 public function exportRoutines($db, array $aliases = [])
595 {
596 global $crlf, $dbi;
597
598 $db_alias = $db;
599 $this->initAlias($aliases, $db_alias);
600
601 $text = '';
602 $delimiter = '$$';
603
604 $procedure_names = $dbi
605 ->getProceduresOrFunctions($db, 'PROCEDURE');
606 $function_names = $dbi->getProceduresOrFunctions($db, 'FUNCTION');
607
608 if ($procedure_names || $function_names) {
609 $text .= $crlf
610 . 'DELIMITER ' . $delimiter . $crlf;
611
612 if ($procedure_names) {
613 $text .= $this->exportRoutineSQL(
614 $db,
615 $aliases,
616 'PROCEDURE',
617 __('Procedures'),
618 $procedure_names,
619 $delimiter
620 );
621 }
622
623 if ($function_names) {
624 $text .= $this->exportRoutineSQL(
625 $db,
626 $aliases,
627 'FUNCTION',
628 __('Functions'),
629 $function_names,
630 $delimiter
631 );
632 }
633
634 $text .= 'DELIMITER ;' . $crlf;
635 }
636
637 if (! empty($text)) {
638 return $this->export->outputHandler($text);
639 }
640
641 return false;
642 }
643
644 /**
645 * Possibly outputs comment
646 *
647 * @param string $text Text of comment
648 *
649 * @return string The formatted comment
650 */
651 private function exportComment($text = '')
652 {
653 if (isset($GLOBALS['sql_include_comments'])
654 && $GLOBALS['sql_include_comments']
655 ) {
656 // see https://dev.mysql.com/doc/refman/5.0/en/ansi-diff-comments.html
657 if (empty($text)) {
658 return '--' . $GLOBALS['crlf'];
659 }
660
661 $lines = preg_split("/\\r\\n|\\r|\\n/", $text);
662 $result = [];
663 foreach ($lines as $line) {
664 $result[] = '-- ' . $line . $GLOBALS['crlf'];
665 }
666
667 return implode('', $result);
668 }
669
670 return '';
671 }
672
673 /**
674 * Possibly outputs CRLF
675 *
676 * @return string crlf or nothing
677 */
678 private function possibleCRLF()
679 {
680 if (isset($GLOBALS['sql_include_comments'])
681 && $GLOBALS['sql_include_comments']
682 ) {
683 return $GLOBALS['crlf'];
684 }
685
686 return '';
687 }
688
689 /**
690 * Outputs export footer
691 *
692 * @return bool Whether it succeeded
693 */
694 public function exportFooter()
695 {
696 global $crlf, $dbi;
697
698 $foot = '';
699
700 if (isset($GLOBALS['sql_disable_fk'])) {
701 $foot .= 'SET FOREIGN_KEY_CHECKS=1;' . $crlf;
702 }
703
704 if (isset($GLOBALS['sql_use_transaction'])) {
705 $foot .= 'COMMIT;' . $crlf;
706 }
707
708 // restore connection settings
709 if ($this->sentCharset) {
710 $foot .= $crlf
711 . '/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;'
712 . $crlf
713 . '/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;'
714 . $crlf
715 . '/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;'
716 . $crlf;
717 $this->sentCharset = false;
718 }
719
720 /* Restore timezone */
721 if (isset($GLOBALS['sql_utc_time']) && $GLOBALS['sql_utc_time']) {
722 $dbi->query('SET time_zone = "' . $GLOBALS['old_tz'] . '"');
723 }
724
725 return $this->export->outputHandler($foot);
726 }
727
728 /**
729 * Outputs export header. It is the first method to be called, so all
730 * the required variables are initialized here.
731 *
732 * @return bool Whether it succeeded
733 */
734 public function exportHeader()
735 {
736 global $crlf, $cfg, $dbi;
737
738 if (isset($GLOBALS['sql_compatibility'])) {
739 $tmp_compat = $GLOBALS['sql_compatibility'];
740 if ($tmp_compat === 'NONE') {
741 $tmp_compat = '';
742 }
743 $dbi->tryQuery('SET SQL_MODE="' . $tmp_compat . '"');
744 unset($tmp_compat);
745 }
746 $head = $this->exportComment('phpMyAdmin SQL Dump')
747 . $this->exportComment('version ' . PMA_VERSION)
748 . $this->exportComment('https://www.phpmyadmin.net/')
749 . $this->exportComment();
750 $host_string = __('Host:') . ' ' . $cfg['Server']['host'];
751 if (! empty($cfg['Server']['port'])) {
752 $host_string .= ':' . $cfg['Server']['port'];
753 }
754 $head .= $this->exportComment($host_string);
755 $head .= $this->exportComment(
756 __('Generation Time:') . ' '
757 . Util::localisedDate()
758 )
759 . $this->exportComment(
760 __('Server version:') . ' ' . $dbi->getVersionString()
761 )
762 . $this->exportComment(__('PHP Version:') . ' ' . PHP_VERSION)
763 . $this->possibleCRLF();
764
765 if (isset($GLOBALS['sql_header_comment'])
766 && ! empty($GLOBALS['sql_header_comment'])
767 ) {
768 // '\n' is not a newline (like "\n" would be), it's the characters
769 // backslash and n, as explained on the export interface
770 $lines = explode('\n', $GLOBALS['sql_header_comment']);
771 $head .= $this->exportComment();
772 foreach ($lines as $one_line) {
773 $head .= $this->exportComment($one_line);
774 }
775 $head .= $this->exportComment();
776 }
777
778 if (isset($GLOBALS['sql_disable_fk'])) {
779 $head .= 'SET FOREIGN_KEY_CHECKS=0;' . $crlf;
780 }
781
782 // We want exported AUTO_INCREMENT columns to have still same value,
783 // do this only for recent MySQL exports
784 if (! isset($GLOBALS['sql_compatibility'])
785 || $GLOBALS['sql_compatibility'] === 'NONE'
786 ) {
787 $head .= 'SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";' . $crlf;
788 }
789
790 if (isset($GLOBALS['sql_use_transaction'])) {
791 $head .= 'START TRANSACTION;' . $crlf;
792 }
793
794 /* Change timezone if we should export timestamps in UTC */
795 if (isset($GLOBALS['sql_utc_time']) && $GLOBALS['sql_utc_time']) {
796 $head .= 'SET time_zone = "+00:00";' . $crlf;
797 $GLOBALS['old_tz'] = $dbi
798 ->fetchValue('SELECT @@session.time_zone');
799 $dbi->query('SET time_zone = "+00:00"');
800 }
801
802 $head .= $this->possibleCRLF();
803
804 if (! empty($GLOBALS['asfile'])) {
805 // we are saving as file, therefore we provide charset information
806 // so that a utility like the mysql client can interpret
807 // the file correctly
808 if (isset($GLOBALS['charset'], Charsets::$mysqlCharsetMap[$GLOBALS['charset']])) {
809 // we got a charset from the export dialog
810 $set_names = Charsets::$mysqlCharsetMap[$GLOBALS['charset']];
811 } else {
812 // by default we use the connection charset
813 $set_names = Charsets::$mysqlCharsetMap['utf-8'];
814 }
815 if ($set_names === 'utf8' && $dbi->getVersion() > 50503) {
816 $set_names = 'utf8mb4';
817 }
818 $head .= $crlf
819 . '/*!40101 SET @OLD_CHARACTER_SET_CLIENT='
820 . '@@CHARACTER_SET_CLIENT */;' . $crlf
821 . '/*!40101 SET @OLD_CHARACTER_SET_RESULTS='
822 . '@@CHARACTER_SET_RESULTS */;' . $crlf
823 . '/*!40101 SET @OLD_COLLATION_CONNECTION='
824 . '@@COLLATION_CONNECTION */;' . $crlf
825 . '/*!40101 SET NAMES ' . $set_names . ' */;' . $crlf . $crlf;
826 $this->sentCharset = true;
827 }
828
829 return $this->export->outputHandler($head);
830 }
831
832 /**
833 * Outputs CREATE DATABASE statement
834 *
835 * @param string $db Database name
836 * @param string $export_type 'server', 'database', 'table'
837 * @param string $db_alias Aliases of db
838 *
839 * @return bool Whether it succeeded
840 */
841 public function exportDBCreate($db, $export_type, $db_alias = '')
842 {
843 global $crlf, $dbi;
844
845 if (empty($db_alias)) {
846 $db_alias = $db;
847 }
848 if (isset($GLOBALS['sql_compatibility'])) {
849 $compat = $GLOBALS['sql_compatibility'];
850 } else {
851 $compat = 'NONE';
852 }
853 if (isset($GLOBALS['sql_drop_database'])) {
854 if (! $this->export->outputHandler(
855 'DROP DATABASE IF EXISTS '
856 . Util::backquoteCompat(
857 $db_alias,
858 $compat,
859 isset($GLOBALS['sql_backquotes'])
860 )
861 . ';' . $crlf
862 )
863 ) {
864 return false;
865 }
866 }
867 if ($export_type === 'database' && ! isset($GLOBALS['sql_create_database'])) {
868 return true;
869 }
870
871 $create_query = 'CREATE DATABASE IF NOT EXISTS '
872 . Util::backquoteCompat(
873 $db_alias,
874 $compat,
875 isset($GLOBALS['sql_backquotes'])
876 );
877 $collation = $dbi->getDbCollation($db);
878 if (mb_strpos($collation, '_')) {
879 $create_query .= ' DEFAULT CHARACTER SET '
880 . mb_substr(
881 $collation,
882 0,
883 (int) mb_strpos($collation, '_')
884 )
885 . ' COLLATE ' . $collation;
886 } else {
887 $create_query .= ' DEFAULT CHARACTER SET ' . $collation;
888 }
889 $create_query .= ';' . $crlf;
890 if (! $this->export->outputHandler($create_query)) {
891 return false;
892 }
893
894 return $this->exportUseStatement($db_alias, $compat);
895 }
896
897 /**
898 * Outputs USE statement
899 *
900 * @param string $db db to use
901 * @param string $compat sql compatibility
902 *
903 * @return bool Whether it succeeded
904 */
905 private function exportUseStatement($db, $compat)
906 {
907 global $crlf;
908
909 if (isset($GLOBALS['sql_compatibility'])
910 && $GLOBALS['sql_compatibility'] === 'NONE'
911 ) {
912 $result = $this->export->outputHandler(
913 'USE '
914 . Util::backquoteCompat(
915 $db,
916 $compat,
917 isset($GLOBALS['sql_backquotes'])
918 )
919 . ';' . $crlf
920 );
921 } else {
922 $result = $this->export->outputHandler('USE ' . $db . ';' . $crlf);
923 }
924
925 return $result;
926 }
927
928 /**
929 * Outputs database header
930 *
931 * @param string $db Database name
932 * @param string $db_alias Alias of db
933 *
934 * @return bool Whether it succeeded
935 */
936 public function exportDBHeader($db, $db_alias = '')
937 {
938 if (empty($db_alias)) {
939 $db_alias = $db;
940 }
941 if (isset($GLOBALS['sql_compatibility'])) {
942 $compat = $GLOBALS['sql_compatibility'];
943 } else {
944 $compat = 'NONE';
945 }
946 $head = $this->exportComment()
947 . $this->exportComment(
948 __('Database:') . ' '
949 . Util::backquoteCompat(
950 $db_alias,
951 $compat,
952 isset($GLOBALS['sql_backquotes'])
953 )
954 )
955 . $this->exportComment();
956
957 return $this->export->outputHandler($head);
958 }
959
960 /**
961 * Outputs database footer
962 *
963 * @param string $db Database name
964 *
965 * @return bool Whether it succeeded
966 */
967 public function exportDBFooter($db)
968 {
969 global $crlf;
970
971 $result = true;
972
973 //add indexes to the sql dump file
974 if (isset($GLOBALS['sql_indexes'])) {
975 $result = $this->export->outputHandler($GLOBALS['sql_indexes']);
976 unset($GLOBALS['sql_indexes']);
977 }
978 //add auto increments to the sql dump file
979 if (isset($GLOBALS['sql_auto_increments'])) {
980 $result = $this->export->outputHandler($GLOBALS['sql_auto_increments']);
981 unset($GLOBALS['sql_auto_increments']);
982 }
983 //add constraints to the sql dump file
984 if (isset($GLOBALS['sql_constraints'])) {
985 $result = $this->export->outputHandler($GLOBALS['sql_constraints']);
986 unset($GLOBALS['sql_constraints']);
987 }
988
989 return $result;
990 }
991
992 /**
993 * Exports events
994 *
995 * @param string $db Database
996 *
997 * @return bool Whether it succeeded
998 */
999 public function exportEvents($db)
1000 {
1001 global $crlf, $dbi;
1002
1003 $text = '';
1004 $delimiter = '$$';
1005
1006 $event_names = $dbi->fetchResult(
1007 'SELECT EVENT_NAME FROM information_schema.EVENTS WHERE'
1008 . " EVENT_SCHEMA= '" . $dbi->escapeString($db)
1009 . "';"
1010 );
1011
1012 if ($event_names) {
1013 $text .= $crlf
1014 . 'DELIMITER ' . $delimiter . $crlf;
1015
1016 $text .= $this->exportComment()
1017 . $this->exportComment(__('Events'))
1018 . $this->exportComment();
1019
1020 foreach ($event_names as $event_name) {
1021 if (! empty($GLOBALS['sql_drop_table'])) {
1022 $text .= 'DROP EVENT IF EXISTS '
1023 . Util::backquote($event_name)
1024 . $delimiter . $crlf;
1025 }
1026 $text .= $dbi->getDefinition($db, 'EVENT', $event_name)
1027 . $delimiter . $crlf . $crlf;
1028 }
1029
1030 $text .= 'DELIMITER ;' . $crlf;
1031 }
1032
1033 if (! empty($text)) {
1034 return $this->export->outputHandler($text);
1035 }
1036
1037 return false;
1038 }
1039
1040 /**
1041 * Exports metadata from Configuration Storage
1042 *
1043 * @param string $db database being exported
1044 * @param string|array $tables table(s) being exported
1045 * @param array $metadataTypes types of metadata to export
1046 *
1047 * @return bool Whether it succeeded
1048 */
1049 public function exportMetadata(
1050 $db,
1051 $tables,
1052 array $metadataTypes
1053 ) {
1054 $cfgRelation = $this->relation->getRelationsParam();
1055 if (! isset($cfgRelation['db'])) {
1056 return true;
1057 }
1058
1059 $comment = $this->possibleCRLF()
1060 . $this->possibleCRLF()
1061 . $this->exportComment()
1062 . $this->exportComment(__('Metadata'))
1063 . $this->exportComment();
1064 if (! $this->export->outputHandler($comment)) {
1065 return false;
1066 }
1067
1068 if (! $this->exportUseStatement(
1069 $cfgRelation['db'],
1070 $GLOBALS['sql_compatibility']
1071 )
1072 ) {
1073 return false;
1074 }
1075
1076 $r = true;
1077 if (is_array($tables)) {
1078 // export metadata for each table
1079 foreach ($tables as $table) {
1080 $r &= $this->exportConfigurationMetadata($db, $table, $metadataTypes);
1081 }
1082 // export metadata for the database
1083 $r &= $this->exportConfigurationMetadata($db, null, $metadataTypes);
1084 } else {
1085 // export metadata for single table
1086 $r &= $this->exportConfigurationMetadata($db, $tables, $metadataTypes);
1087 }
1088
1089 return (bool) $r;
1090 }
1091
1092 /**
1093 * Exports metadata from Configuration Storage
1094 *
1095 * @param string $db database being exported
1096 * @param string|null $table table being exported
1097 * @param array $metadataTypes types of metadata to export
1098 *
1099 * @return bool Whether it succeeded
1100 */
1101 private function exportConfigurationMetadata(
1102 $db,
1103 $table,
1104 array $metadataTypes
1105 ) {
1106 global $dbi;
1107
1108 $cfgRelation = $this->relation->getRelationsParam();
1109
1110 if (isset($table)) {
1111 $types = [
1112 'column_info' => 'db_name',
1113 'table_uiprefs' => 'db_name',
1114 'tracking' => 'db_name',
1115 ];
1116 } else {
1117 $types = [
1118 'bookmark' => 'dbase',
1119 'relation' => 'master_db',
1120 'pdf_pages' => 'db_name',
1121 'savedsearches' => 'db_name',
1122 'central_columns' => 'db_name',
1123 ];
1124 }
1125
1126 $aliases = [];
1127
1128 $comment = $this->possibleCRLF()
1129 . $this->exportComment();
1130
1131 if (isset($table)) {
1132 $comment .= $this->exportComment(
1133 sprintf(
1134 __('Metadata for table %s'),
1135 $table
1136 )
1137 );
1138 } else {
1139 $comment .= $this->exportComment(
1140 sprintf(
1141 __('Metadata for database %s'),
1142 $db
1143 )
1144 );
1145 }
1146
1147 $comment .= $this->exportComment();
1148
1149 if (! $this->export->outputHandler($comment)) {
1150 return false;
1151 }
1152
1153 foreach ($types as $type => $dbNameColumn) {
1154 if (! in_array($type, $metadataTypes) || ! isset($cfgRelation[$type])) {
1155 continue;
1156 }
1157
1158 // special case, designer pages and their coordinates
1159 if ($type === 'pdf_pages') {
1160 $sql_query = 'SELECT `page_nr`, `page_descr` FROM '
1161 . Util::backquote($cfgRelation['db'])
1162 . '.' . Util::backquote($cfgRelation[$type])
1163 . ' WHERE ' . Util::backquote($dbNameColumn)
1164 . " = '" . $dbi->escapeString($db) . "'";
1165
1166 $result = $dbi->fetchResult(
1167 $sql_query,
1168 'page_nr',
1169 'page_descr'
1170 );
1171
1172 foreach ($result as $page => $name) {
1173 // insert row for pdf_page
1174 $sql_query_row = 'SELECT `db_name`, `page_descr` FROM '
1175 . Util::backquote($cfgRelation['db'])
1176 . '.' . Util::backquote(
1177 $cfgRelation[$type]
1178 )
1179 . ' WHERE ' . Util::backquote(
1180 $dbNameColumn
1181 )
1182 . " = '" . $dbi->escapeString($db) . "'"
1183 . " AND `page_nr` = '" . intval($page) . "'";
1184
1185 if (! $this->exportData(
1186 $cfgRelation['db'],
1187 $cfgRelation[$type],
1188 $GLOBALS['crlf'],
1189 '',
1190 $sql_query_row,
1191 $aliases
1192 )
1193 ) {
1194 return false;
1195 }
1196
1197 $lastPage = $GLOBALS['crlf']
1198 . 'SET @LAST_PAGE = LAST_INSERT_ID();'
1199 . $GLOBALS['crlf'];
1200 if (! $this->export->outputHandler($lastPage)) {
1201 return false;
1202 }
1203
1204 $sql_query_coords = 'SELECT `db_name`, `table_name`, '
1205 . "'@LAST_PAGE' AS `pdf_page_number`, `x`, `y` FROM "
1206 . Util::backquote($cfgRelation['db'])
1207 . '.' . Util::backquote(
1208 $cfgRelation['table_coords']
1209 )
1210 . " WHERE `pdf_page_number` = '" . $page . "'";
1211
1212 $GLOBALS['exporting_metadata'] = true;
1213 if (! $this->exportData(
1214 $cfgRelation['db'],
1215 $cfgRelation['table_coords'],
1216 $GLOBALS['crlf'],
1217 '',
1218 $sql_query_coords,
1219 $aliases
1220 )
1221 ) {
1222 $GLOBALS['exporting_metadata'] = false;
1223
1224 return false;
1225 }
1226 $GLOBALS['exporting_metadata'] = false;
1227 }
1228 continue;
1229 }
1230
1231 // remove auto_incrementing id field for some tables
1232 if ($type === 'bookmark') {
1233 $sql_query = 'SELECT `dbase`, `user`, `label`, `query` FROM ';
1234 } elseif ($type === 'column_info') {
1235 $sql_query = 'SELECT `db_name`, `table_name`, `column_name`,'
1236 . ' `comment`, `mimetype`, `transformation`,'
1237 . ' `transformation_options`, `input_transformation`,'
1238 . ' `input_transformation_options` FROM';
1239 } elseif ($type === 'savedsearches') {
1240 $sql_query = 'SELECT `username`, `db_name`, `search_name`,'
1241 . ' `search_data` FROM';
1242 } else {
1243 $sql_query = 'SELECT * FROM ';
1244 }
1245 $sql_query .= Util::backquote($cfgRelation['db'])
1246 . '.' . Util::backquote($cfgRelation[$type])
1247 . ' WHERE ' . Util::backquote($dbNameColumn)
1248 . " = '" . $dbi->escapeString($db) . "'";
1249 if (isset($table)) {
1250 $sql_query .= " AND `table_name` = '"
1251 . $dbi->escapeString($table) . "'";
1252 }
1253
1254 if (! $this->exportData(
1255 $cfgRelation['db'],
1256 $cfgRelation[$type],
1257 $GLOBALS['crlf'],
1258 '',
1259 $sql_query,
1260 $aliases
1261 )
1262 ) {
1263 return false;
1264 }
1265 }
1266
1267 return true;
1268 }
1269
1270 /**
1271 * Returns a stand-in CREATE definition to resolve view dependencies
1272 *
1273 * @param string $db the database name
1274 * @param string $view the view name
1275 * @param string $crlf the end of line sequence
1276 * @param array $aliases Aliases of db/table/columns
1277 *
1278 * @return string resulting definition
1279 */
1280 public function getTableDefStandIn($db, $view, $crlf, $aliases = [])
1281 {
1282 global $dbi;
1283
1284 $db_alias = $db;
1285 $view_alias = $view;
1286 $this->initAlias($aliases, $db_alias, $view_alias);
1287 $create_query = '';
1288 if (! empty($GLOBALS['sql_drop_table'])) {
1289 $create_query .= 'DROP VIEW IF EXISTS '
1290 . Util::backquote($view_alias)
1291 . ';' . $crlf;
1292 }
1293
1294 $create_query .= 'CREATE TABLE ';
1295
1296 if (isset($GLOBALS['sql_if_not_exists'])
1297 && $GLOBALS['sql_if_not_exists']
1298 ) {
1299 $create_query .= 'IF NOT EXISTS ';
1300 }
1301 $create_query .= Util::backquote($view_alias) . ' (' . $crlf;
1302 $tmp = [];
1303 $columns = $dbi->getColumnsFull($db, $view);
1304 foreach ($columns as $column_name => $definition) {
1305 $col_alias = $column_name;
1306 if (! empty($aliases[$db]['tables'][$view]['columns'][$col_alias])) {
1307 $col_alias = $aliases[$db]['tables'][$view]['columns'][$col_alias];
1308 }
1309 $tmp[] = Util::backquote($col_alias) . ' ' .
1310 $definition['Type'] . $crlf;
1311 }
1312
1313 return $create_query . implode(',', $tmp) . ');' . $crlf;
1314 }
1315
1316 /**
1317 * Returns CREATE definition that matches $view's structure
1318 *
1319 * @param string $db the database name
1320 * @param string $view the view name
1321 * @param string $crlf the end of line sequence
1322 * @param bool $add_semicolon whether to add semicolon and end-of-line at
1323 * the end
1324 * @param array $aliases Aliases of db/table/columns
1325 *
1326 * @return string resulting schema
1327 */
1328 private function getTableDefForView(
1329 $db,
1330 $view,
1331 $crlf,
1332 $add_semicolon = true,
1333 array $aliases = []
1334 ) {
1335 global $dbi;
1336
1337 $db_alias = $db;
1338 $view_alias = $view;
1339 $this->initAlias($aliases, $db_alias, $view_alias);
1340 $create_query = 'CREATE TABLE';
1341 if (isset($GLOBALS['sql_if_not_exists'])) {
1342 $create_query .= ' IF NOT EXISTS ';
1343 }
1344 $create_query .= Util::backquote($view_alias) . '(' . $crlf;
1345
1346 $columns = $dbi->getColumns($db, $view, null, true);
1347
1348 $firstCol = true;
1349 foreach ($columns as $column) {
1350 $col_alias = $column['Field'];
1351 if (! empty($aliases[$db]['tables'][$view]['columns'][$col_alias])) {
1352 $col_alias = $aliases[$db]['tables'][$view]['columns'][$col_alias];
1353 }
1354 $extracted_columnspec = Util::extractColumnSpec(
1355 $column['Type']
1356 );
1357
1358 if (! $firstCol) {
1359 $create_query .= ',' . $crlf;
1360 }
1361 $create_query .= ' ' . Util::backquote($col_alias);
1362 $create_query .= ' ' . $column['Type'];
1363 if ($extracted_columnspec['can_contain_collation']
1364 && ! empty($column['Collation'])
1365 ) {
1366 $create_query .= ' COLLATE ' . $column['Collation'];
1367 }
1368 if ($column['Null'] === 'NO') {
1369 $create_query .= ' NOT NULL';
1370 }
1371 if (isset($column['Default'])) {
1372 $create_query .= " DEFAULT '"
1373 . $dbi->escapeString($column['Default']) . "'";
1374 } else {
1375 if ($column['Null'] === 'YES') {
1376 $create_query .= ' DEFAULT NULL';
1377 }
1378 }
1379 if (! empty($column['Comment'])) {
1380 $create_query .= " COMMENT '"
1381 . $dbi->escapeString($column['Comment']) . "'";
1382 }
1383 $firstCol = false;
1384 }
1385 $create_query .= $crlf . ')' . ($add_semicolon ? ';' : '') . $crlf;
1386
1387 if (isset($GLOBALS['sql_compatibility'])) {
1388 $compat = $GLOBALS['sql_compatibility'];
1389 } else {
1390 $compat = 'NONE';
1391 }
1392 if ($compat === 'MSSQL') {
1393 $create_query = $this->makeCreateTableMSSQLCompatible(
1394 $create_query
1395 );
1396 }
1397
1398 return $create_query;
1399 }
1400
1401 /**
1402 * Returns $table's CREATE definition
1403 *
1404 * @param string $db the database name
1405 * @param string $table the table name
1406 * @param string $crlf the end of line sequence
1407 * @param string $error_url the url to go back in case
1408 * of error
1409 * @param bool $show_dates whether to include creation/
1410 * update/check dates
1411 * @param bool $add_semicolon whether to add semicolon and
1412 * end-of-line at the end
1413 * @param bool $view whether we're handling a view
1414 * @param bool $update_indexes_increments whether we need to update
1415 * two global variables
1416 * @param array $aliases Aliases of db/table/columns
1417 *
1418 * @return string resulting schema
1419 */
1420 public function getTableDef(
1421 $db,
1422 $table,
1423 $crlf,
1424 $error_url,
1425 $show_dates = false,
1426 $add_semicolon = true,
1427 $view = false,
1428 $update_indexes_increments = true,
1429 array $aliases = []
1430 ) {
1431 global $sql_drop_table, $sql_backquotes, $sql_constraints,
1432 $sql_constraints_query, $sql_indexes, $sql_indexes_query,
1433 $sql_auto_increments, $sql_drop_foreign_keys, $dbi;
1434
1435 $db_alias = $db;
1436 $table_alias = $table;
1437 $this->initAlias($aliases, $db_alias, $table_alias);
1438
1439 $schema_create = '';
1440 $auto_increment = '';
1441 $new_crlf = $crlf;
1442
1443 if (isset($GLOBALS['sql_compatibility'])) {
1444 $compat = $GLOBALS['sql_compatibility'];
1445 } else {
1446 $compat = 'NONE';
1447 }
1448
1449 // need to use PhpMyAdmin\DatabaseInterface::QUERY_STORE
1450 // with $dbi->numRows() in mysqli
1451 $result = $dbi->tryQuery(
1452 'SHOW TABLE STATUS FROM ' . Util::backquote($db)
1453 . ' WHERE Name = \'' . $dbi->escapeString((string) $table) . '\'',
1454 DatabaseInterface::CONNECT_USER,
1455 DatabaseInterface::QUERY_STORE
1456 );
1457 if ($result != false) {
1458 if ($dbi->numRows($result) > 0) {
1459 $tmpres = $dbi->fetchAssoc($result);
1460
1461 // Here we optionally add the AUTO_INCREMENT next value,
1462 // but starting with MySQL 5.0.24, the clause is already included
1463 // in SHOW CREATE TABLE so we'll remove it below
1464 if (isset($GLOBALS['sql_auto_increment'])
1465 && ! empty($tmpres['Auto_increment'])
1466 ) {
1467 $auto_increment .= ' AUTO_INCREMENT='
1468 . $tmpres['Auto_increment'] . ' ';
1469 }
1470
1471 if ($show_dates
1472 && isset($tmpres['Create_time'])
1473 && ! empty($tmpres['Create_time'])
1474 ) {
1475 $schema_create .= $this->exportComment(
1476 __('Creation:') . ' '
1477 . Util::localisedDate(
1478 strtotime($tmpres['Create_time'])
1479 )
1480 );
1481 $new_crlf = $this->exportComment() . $crlf;
1482 }
1483
1484 if ($show_dates
1485 && isset($tmpres['Update_time'])
1486 && ! empty($tmpres['Update_time'])
1487 ) {
1488 $schema_create .= $this->exportComment(
1489 __('Last update:') . ' '
1490 . Util::localisedDate(
1491 strtotime($tmpres['Update_time'])
1492 )
1493 );
1494 $new_crlf = $this->exportComment() . $crlf;
1495 }
1496
1497 if ($show_dates
1498 && isset($tmpres['Check_time'])
1499 && ! empty($tmpres['Check_time'])
1500 ) {
1501 $schema_create .= $this->exportComment(
1502 __('Last check:') . ' '
1503 . Util::localisedDate(
1504 strtotime($tmpres['Check_time'])
1505 )
1506 );
1507 $new_crlf = $this->exportComment() . $crlf;
1508 }
1509 }
1510 $dbi->freeResult($result);
1511 }
1512
1513 $schema_create .= $new_crlf;
1514
1515 if (! empty($sql_drop_table)
1516 && $dbi->getTable($db, $table)->isView()
1517 ) {
1518 $schema_create .= 'DROP VIEW IF EXISTS '
1519 . Util::backquote($table_alias, $sql_backquotes) . ';'
1520 . $crlf;
1521 }
1522
1523 // no need to generate a DROP VIEW here, it was done earlier
1524 if (! empty($sql_drop_table)
1525 && ! $dbi->getTable($db, $table)->isView()
1526 ) {
1527 $schema_create .= 'DROP TABLE IF EXISTS '
1528 . Util::backquote($table_alias, $sql_backquotes) . ';'
1529 . $crlf;
1530 }
1531
1532 // Complete table dump,
1533 // Whether to quote table and column names or not
1534 if ($sql_backquotes) {
1535 $dbi->query('SET SQL_QUOTE_SHOW_CREATE = 1');
1536 } else {
1537 $dbi->query('SET SQL_QUOTE_SHOW_CREATE = 0');
1538 }
1539
1540 // I don't see the reason why this unbuffered query could cause problems,
1541 // because SHOW CREATE TABLE returns only one row, and we free the
1542 // results below. Nonetheless, we got 2 user reports about this
1543 // (see bug 1562533) so I removed the unbuffered mode.
1544 // $result = $dbi->query('SHOW CREATE TABLE ' . backquote($db)
1545 // . '.' . backquote($table), null, DatabaseInterface::QUERY_UNBUFFERED);
1546 //
1547 // Note: SHOW CREATE TABLE, at least in MySQL 5.1.23, does not
1548 // produce a displayable result for the default value of a BIT
1549 // column, nor does the mysqldump command. See MySQL bug 35796
1550 $dbi->tryQuery('USE ' . Util::backquote($db));
1551 $result = $dbi->tryQuery(
1552 'SHOW CREATE TABLE ' . Util::backquote($db) . '.'
1553 . Util::backquote($table)
1554 );
1555 // an error can happen, for example the table is crashed
1556 $tmp_error = $dbi->getError();
1557 if ($tmp_error) {
1558 $message = sprintf(__('Error reading structure for table %s:'), $db . '.' . $table);
1559 $message .= ' ' . $tmp_error;
1560 if (! defined('TESTSUITE')) {
1561 trigger_error($message, E_USER_ERROR);
1562 }
1563
1564 return $this->exportComment($message);
1565 }
1566
1567 // Old mode is stored so it can be restored once exporting is done.
1568 $old_mode = Context::$MODE;
1569
1570 $warning = '';
1571
1572 $row = null;
1573 if ($result !== false) {
1574 $row = $dbi->fetchRow($result);
1575 }
1576
1577 if ($row) {
1578 $create_query = $row[1];
1579 unset($row);
1580
1581 // Convert end of line chars to one that we want (note that MySQL
1582 // doesn't return query it will accept in all cases)
1583 if (mb_strpos($create_query, "(\r\n ")) {
1584 $create_query = str_replace("\r\n", $crlf, $create_query);
1585 } elseif (mb_strpos($create_query, "(\n ")) {
1586 $create_query = str_replace("\n", $crlf, $create_query);
1587 } elseif (mb_strpos($create_query, "(\r ")) {
1588 $create_query = str_replace("\r", $crlf, $create_query);
1589 }
1590
1591 /*
1592 * Drop database name from VIEW creation.
1593 *
1594 * This is a bit tricky, but we need to issue SHOW CREATE TABLE with
1595 * database name, but we don't want name to show up in CREATE VIEW
1596 * statement.
1597 */
1598 if ($view) {
1599 //TODO: use parser
1600 $create_query = preg_replace(
1601 '/' . preg_quote(Util::backquote($db), '/') . '\./',
1602 '',
1603 $create_query
1604 );
1605 $parser = new Parser($create_query);
1606 /**
1607 * `CREATE TABLE` statement.
1608 *
1609 * @var CreateStatement
1610 */
1611 $statement = $parser->statements[0];
1612
1613 // exclude definition of current user
1614 if (isset($GLOBALS['sql_view_current_user'])) {
1615 $statement->options->remove('DEFINER');
1616 }
1617
1618 if (isset($GLOBALS['sql_simple_view_export'])) {
1619 $statement->options->remove('SQL SECURITY');
1620 $statement->options->remove('INVOKER');
1621 $statement->options->remove('ALGORITHM');
1622 $statement->options->remove('DEFINER');
1623 }
1624 $create_query = $statement->build();
1625
1626 // whether to replace existing view or not
1627 if (isset($GLOBALS['sql_or_replace_view'])) {
1628 $create_query = preg_replace(
1629 '/^CREATE/',
1630 'CREATE OR REPLACE',
1631 $create_query
1632 );
1633 }
1634 }
1635
1636 // Substitute aliases in `CREATE` query.
1637 $create_query = $this->replaceWithAliases(
1638 $create_query,
1639 $aliases,
1640 $db,
1641 $table,
1642 $flag
1643 );
1644
1645 // One warning per view.
1646 if ($flag && $view) {
1647 $warning = $this->exportComment()
1648 . $this->exportComment(
1649 __('It appears your database uses views;')
1650 )
1651 . $this->exportComment(
1652 __('alias export may not work reliably in all cases.')
1653 )
1654 . $this->exportComment();
1655 }
1656
1657 // Adding IF NOT EXISTS, if required.
1658 if (isset($GLOBALS['sql_if_not_exists'])) {
1659 $create_query = preg_replace(
1660 '/^CREATE TABLE/',
1661 'CREATE TABLE IF NOT EXISTS',
1662 $create_query
1663 );
1664 }
1665
1666 // Making the query MSSQL compatible.
1667 if ($compat === 'MSSQL') {
1668 $create_query = $this->makeCreateTableMSSQLCompatible(
1669 $create_query
1670 );
1671 }
1672
1673 // Views have no constraints, indexes, etc. They do not require any
1674 // analysis.
1675 if (! $view) {
1676 if (empty($sql_backquotes)) {
1677 // Option "Enclose table and column names with backquotes"
1678 // was checked.
1679 Context::$MODE |= Context::SQL_MODE_NO_ENCLOSING_QUOTES;
1680 }
1681
1682 // Using appropriate quotes.
1683 if (($compat === 'MSSQL') || ($sql_backquotes === '"')) {
1684 Context::$MODE |= Context::SQL_MODE_ANSI_QUOTES;
1685 }
1686 }
1687
1688 /**
1689 * Parser used for analysis.
1690 *
1691 * @var Parser
1692 */
1693 $parser = new Parser($create_query);
1694
1695 /**
1696 * `CREATE TABLE` statement.
1697 *
1698 * @var CreateStatement
1699 */
1700 $statement = $parser->statements[0];
1701
1702 if (! empty($statement->entityOptions)) {
1703 $engine = $statement->entityOptions->has('ENGINE');
1704 } else {
1705 $engine = '';
1706 }
1707
1708 /* Avoid operation on ARCHIVE tables as those can not be altered */
1709 if (! empty($statement->fields) && (empty($engine) || strtoupper($engine) !== 'ARCHIVE')) {
1710
1711 /**
1712 * Fragments containing definition of each constraint.
1713 *
1714 * @var array
1715 */
1716 $constraints = [];
1717
1718 /**
1719 * Fragments containing definition of each index.
1720 *
1721 * @var array
1722 */
1723 $indexes = [];
1724
1725 /**
1726 * Fragments containing definition of each FULLTEXT index.
1727 *
1728 * @var array
1729 */
1730 $indexes_fulltext = [];
1731
1732 /**
1733 * Fragments containing definition of each foreign key that will
1734 * be dropped.
1735 *
1736 * @var array
1737 */
1738 $dropped = [];
1739
1740 /**
1741 * Fragment containing definition of the `AUTO_INCREMENT`.
1742 *
1743 * @var array
1744 */
1745 $auto_increment = [];
1746
1747 // Scanning each field of the `CREATE` statement to fill the arrays
1748 // above.
1749 // If the field is used in any of the arrays above, it is removed
1750 // from the original definition.
1751 // Also, AUTO_INCREMENT attribute is removed.
1752 /** @var CreateDefinition $field */
1753 foreach ($statement->fields as $key => $field) {
1754 if ($field->isConstraint) {
1755 // Creating the parts that add constraints.
1756 $constraints[] = $field::build($field);
1757 unset($statement->fields[$key]);
1758 } elseif (! empty($field->key)) {
1759 // Creating the parts that add indexes (must not be
1760 // constraints).
1761 if ($field->key->type === 'FULLTEXT KEY') {
1762 $indexes_fulltext[] = $field::build($field);
1763 unset($statement->fields[$key]);
1764 } else {
1765 if (empty($GLOBALS['sql_if_not_exists'])) {
1766 $indexes[] = str_replace(
1767 'COMMENT=\'',
1768 'COMMENT \'',
1769 $field::build($field)
1770 );
1771 unset($statement->fields[$key]);
1772 }
1773 }
1774 }
1775
1776 // Creating the parts that drop foreign keys.
1777 if (! empty($field->key)) {
1778 if ($field->key->type === 'FOREIGN KEY') {
1779 $dropped[] = 'FOREIGN KEY ' . Context::escape(
1780 $field->name
1781 );
1782 unset($statement->fields[$key]);
1783 }
1784 }
1785
1786 // Dropping AUTO_INCREMENT.
1787 if (empty($field->options)) {
1788 continue;
1789 }
1790
1791 if (! $field->options->has('AUTO_INCREMENT')
1792 || ! empty($GLOBALS['sql_if_not_exists'])
1793 ) {
1794 continue;
1795 }
1796
1797 $auto_increment[] = $field::build($field);
1798 $field->options->remove('AUTO_INCREMENT');
1799 }
1800
1801 /**
1802 * The header of the `ALTER` statement (`ALTER TABLE tbl`).
1803 *
1804 * @var string
1805 */
1806 $alter_header = 'ALTER TABLE ' .
1807 Util::backquoteCompat(
1808 $table_alias,
1809 $compat,
1810 $sql_backquotes
1811 );
1812
1813 /**
1814 * The footer of the `ALTER` statement (usually ';')
1815 *
1816 * @var string
1817 */
1818 $alter_footer = ';' . $crlf;
1819
1820 // Generating constraints-related query.
1821 if (! empty($constraints)) {
1822 $sql_constraints_query = $alter_header . $crlf . ' ADD '
1823 . implode(',' . $crlf . ' ADD ', $constraints)
1824 . $alter_footer;
1825
1826 $sql_constraints = $this->generateComment(
1827 $crlf,
1828 $sql_constraints,
1829 __('Constraints for dumped tables'),
1830 __('Constraints for table'),
1831 $table_alias,
1832 $compat
1833 ) . $sql_constraints_query;
1834 }
1835
1836 // Generating indexes-related query.
1837 $sql_indexes_query = '';
1838
1839 if (! empty($indexes)) {
1840 $sql_indexes_query .= $alter_header . $crlf . ' ADD '
1841 . implode(',' . $crlf . ' ADD ', $indexes)
1842 . $alter_footer;
1843 }
1844
1845 if (! empty($indexes_fulltext)) {
1846 // InnoDB supports one FULLTEXT index creation at a time.
1847 // So FULLTEXT indexes are created one-by-one after other
1848 // indexes where created.
1849 $sql_indexes_query .= $alter_header .
1850 ' ADD ' . implode(
1851 $alter_footer . $alter_header . ' ADD ',
1852 $indexes_fulltext
1853 ) . $alter_footer;
1854 }
1855
1856 if (! empty($indexes) || ! empty($indexes_fulltext)) {
1857 $sql_indexes = $this->generateComment(
1858 $crlf,
1859 $sql_indexes,
1860 __('Indexes for dumped tables'),
1861 __('Indexes for table'),
1862 $table_alias,
1863 $compat
1864 ) . $sql_indexes_query;
1865 }
1866
1867 // Generating drop foreign keys-related query.
1868 if (! empty($dropped)) {
1869 $sql_drop_foreign_keys = $alter_header . $crlf . ' DROP '
1870 . implode(',' . $crlf . ' DROP ', $dropped)
1871 . $alter_footer;
1872 }
1873
1874 // Generating auto-increment-related query.
1875 if (! empty($auto_increment) && $update_indexes_increments) {
1876 $sql_auto_increments_query = $alter_header . $crlf . ' MODIFY '
1877 . implode(',' . $crlf . ' MODIFY ', $auto_increment);
1878 if (isset($GLOBALS['sql_auto_increment'])
1879 && ($statement->entityOptions->has('AUTO_INCREMENT') !== false)
1880 ) {
1881 if (! isset($GLOBALS['table_data'])
1882 || (isset($GLOBALS['table_data'])
1883 && in_array($table, $GLOBALS['table_data']))
1884 ) {
1885 $sql_auto_increments_query .= ', AUTO_INCREMENT='
1886 . $statement->entityOptions->has('AUTO_INCREMENT');
1887 }
1888 }
1889 $sql_auto_increments_query .= ';' . $crlf;
1890
1891 $sql_auto_increments = $this->generateComment(
1892 $crlf,
1893 $sql_auto_increments,
1894 __('AUTO_INCREMENT for dumped tables'),
1895 __('AUTO_INCREMENT for table'),
1896 $table_alias,
1897 $compat
1898 ) . $sql_auto_increments_query;
1899 }
1900
1901 // Removing the `AUTO_INCREMENT` attribute from the `CREATE TABLE`
1902 // too.
1903 if (! empty($statement->entityOptions)
1904 && (empty($GLOBALS['sql_if_not_exists'])
1905 || empty($GLOBALS['sql_auto_increment']))
1906 ) {
1907 $statement->entityOptions->remove('AUTO_INCREMENT');
1908 }
1909
1910 // Rebuilding the query.
1911 $create_query = $statement->build();
1912 }
1913
1914 $schema_create .= $create_query;
1915 }
1916
1917 $dbi->freeResult($result);
1918
1919 // Restoring old mode.
1920 Context::$MODE = $old_mode;
1921
1922 return $warning . $schema_create . ($add_semicolon ? ';' . $crlf : '');
1923 }
1924
1925 /**
1926 * Returns $table's comments, relations etc.
1927 *
1928 * @param string $db database name
1929 * @param string $table table name
1930 * @param string $crlf end of line sequence
1931 * @param bool $do_relation whether to include relation comments
1932 * @param bool $do_mime whether to include mime comments
1933 * @param array $aliases Aliases of db/table/columns
1934 *
1935 * @return string resulting comments
1936 */
1937 private function getTableComments(
1938 $db,
1939 $table,
1940 $crlf,
1941 $do_relation = false,
1942 $do_mime = false,
1943 array $aliases = []
1944 ) {
1945 global $cfgRelation, $sql_backquotes;
1946
1947 $db_alias = $db;
1948 $table_alias = $table;
1949 $this->initAlias($aliases, $db_alias, $table_alias);
1950
1951 $schema_create = '';
1952
1953 // Check if we can use Relations
1954 [$res_rel, $have_rel] = $this->relation->getRelationsAndStatus(
1955 $do_relation && ! empty($cfgRelation['relation']),
1956 $db,
1957 $table
1958 );
1959
1960 if ($do_mime && $cfgRelation['mimework']) {
1961 $mime_map = $this->transformations->getMime($db, $table, true);
1962 if ($mime_map === null) {
1963 unset($mime_map);
1964 }
1965 }
1966
1967 if (isset($mime_map) && count($mime_map) > 0) {
1968 $schema_create .= $this->possibleCRLF()
1969 . $this->exportComment()
1970 . $this->exportComment(
1971 __('MEDIA TYPES FOR TABLE') . ' '
1972 . Util::backquote($table, $sql_backquotes) . ':'
1973 );
1974 foreach ($mime_map as $mime_field => $mime) {
1975 $schema_create .= $this->exportComment(
1976 ' '
1977 . Util::backquote($mime_field, $sql_backquotes)
1978 )
1979 . $this->exportComment(
1980 ' '
1981 . Util::backquote(
1982 $mime['mimetype'],
1983 $sql_backquotes
1984 )
1985 );
1986 }
1987 $schema_create .= $this->exportComment();
1988 }
1989
1990 if ($have_rel) {
1991 $schema_create .= $this->possibleCRLF()
1992 . $this->exportComment()
1993 . $this->exportComment(
1994 __('RELATIONSHIPS FOR TABLE') . ' '
1995 . Util::backquote($table_alias, $sql_backquotes)
1996 . ':'
1997 );
1998
1999 foreach ($res_rel as $rel_field => $rel) {
2000 if ($rel_field !== 'foreign_keys_data') {
2001 $rel_field_alias = ! empty(
2002 $aliases[$db]['tables'][$table]['columns'][$rel_field]
2003 ) ? $aliases[$db]['tables'][$table]['columns'][$rel_field]
2004 : $rel_field;
2005 $schema_create .= $this->exportComment(
2006 ' '
2007 . Util::backquote(
2008 $rel_field_alias,
2009 $sql_backquotes
2010 )
2011 )
2012 . $this->exportComment(
2013 ' '
2014 . Util::backquote(
2015 $rel['foreign_table'],
2016 $sql_backquotes
2017 )
2018 . ' -> '
2019 . Util::backquote(
2020 $rel['foreign_field'],
2021 $sql_backquotes
2022 )
2023 );
2024 } else {
2025 foreach ($rel as $one_key) {
2026 foreach ($one_key['index_list'] as $index => $field) {
2027 $rel_field_alias = ! empty(
2028 $aliases[$db]['tables'][$table]['columns'][$field]
2029 ) ? $aliases[$db]['tables'][$table]['columns'][$field]
2030 : $field;
2031 $schema_create .= $this->exportComment(
2032 ' '
2033 . Util::backquote(
2034 $rel_field_alias,
2035 $sql_backquotes
2036 )
2037 )
2038 . $this->exportComment(
2039 ' '
2040 . Util::backquote(
2041 $one_key['ref_table_name'],
2042 $sql_backquotes
2043 )
2044 . ' -> '
2045 . Util::backquote(
2046 $one_key['ref_index_list'][$index],
2047 $sql_backquotes
2048 )
2049 );
2050 }
2051 }
2052 }
2053 }
2054 $schema_create .= $this->exportComment();
2055 }
2056
2057 return $schema_create;
2058 }
2059
2060 /**
2061 * Outputs a raw query
2062 *
2063 * @param string $err_url the url to go back in case of error
2064 * @param string $sql_query the rawquery to output
2065 * @param string $crlf the seperator for a file
2066 *
2067 * @return bool if succeeded
2068 */
2069 public function exportRawQuery(string $err_url, string $sql_query, string $crlf): bool
2070 {
2071 return $this->export->outputHandler($sql_query);
2072 }
2073
2074 /**
2075 * Outputs table's structure
2076 *
2077 * @param string $db database name
2078 * @param string $table table name
2079 * @param string $crlf the end of line sequence
2080 * @param string $error_url the url to go back in case of error
2081 * @param string $export_mode 'create_table','triggers','create_view',
2082 * 'stand_in'
2083 * @param string $export_type 'server', 'database', 'table'
2084 * @param bool $relation whether to include relation comments
2085 * @param bool $comments whether to include the pmadb-style column
2086 * comments as comments in the structure; this is
2087 * deprecated but the parameter is left here
2088 * because /export calls exportStructure()
2089 * also for other export types which use this
2090 * parameter
2091 * @param bool $mime whether to include mime comments
2092 * @param bool $dates whether to include creation/update/check dates
2093 * @param array $aliases Aliases of db/table/columns
2094 *
2095 * @return bool Whether it succeeded
2096 */
2097 public function exportStructure(
2098 $db,
2099 $table,
2100 $crlf,
2101 $error_url,
2102 $export_mode,
2103 $export_type,
2104 $relation = false,
2105 $comments = false,
2106 $mime = false,
2107 $dates = false,
2108 array $aliases = []
2109 ) {
2110 global $dbi;
2111
2112 $db_alias = $db;
2113 $table_alias = $table;
2114 $this->initAlias($aliases, $db_alias, $table_alias);
2115 if (isset($GLOBALS['sql_compatibility'])) {
2116 $compat = $GLOBALS['sql_compatibility'];
2117 } else {
2118 $compat = 'NONE';
2119 }
2120
2121 $formatted_table_name = Util::backquoteCompat(
2122 $table_alias,
2123 $compat,
2124 isset($GLOBALS['sql_backquotes'])
2125 );
2126 $dump = $this->possibleCRLF()
2127 . $this->exportComment(str_repeat('-', 56))
2128 . $this->possibleCRLF()
2129 . $this->exportComment();
2130
2131 switch ($export_mode) {
2132 case 'create_table':
2133 $dump .= $this->exportComment(
2134 __('Table structure for table') . ' ' . $formatted_table_name
2135 );
2136 $dump .= $this->exportComment();
2137 $dump .= $this->getTableDef(
2138 $db,
2139 $table,
2140 $crlf,
2141 $error_url,
2142 $dates,
2143 true,
2144 false,
2145 true,
2146 $aliases
2147 );
2148 $dump .= $this->getTableComments(
2149 $db,
2150 $table,
2151 $crlf,
2152 $relation,
2153 $mime,
2154 $aliases
2155 );
2156 break;
2157 case 'triggers':
2158 $dump = '';
2159 $delimiter = '$$';
2160 $triggers = $dbi->getTriggers($db, $table, $delimiter);
2161 if ($triggers) {
2162 $dump .= $this->possibleCRLF()
2163 . $this->exportComment()
2164 . $this->exportComment(
2165 __('Triggers') . ' ' . $formatted_table_name
2166 )
2167 . $this->exportComment();
2168 $used_alias = false;
2169 $trigger_query = '';
2170 foreach ($triggers as $trigger) {
2171 if (! empty($GLOBALS['sql_drop_table'])) {
2172 $trigger_query .= $trigger['drop'] . ';' . $crlf;
2173 }
2174
2175 $trigger_query .= 'DELIMITER ' . $delimiter . $crlf;
2176 $trigger_query .= $this->replaceWithAliases(
2177 $trigger['create'],
2178 $aliases,
2179 $db,
2180 $table,
2181 $flag
2182 );
2183 if ($flag) {
2184 $used_alias = true;
2185 }
2186 $trigger_query .= 'DELIMITER ;' . $crlf;
2187 }
2188 // One warning per table.
2189 if ($used_alias) {
2190 $dump .= $this->exportComment(
2191 __('It appears your table uses triggers;')
2192 )
2193 . $this->exportComment(
2194 __('alias export may not work reliably in all cases.')
2195 )
2196 . $this->exportComment();
2197 }
2198 $dump .= $trigger_query;
2199 }
2200 break;
2201 case 'create_view':
2202 if (empty($GLOBALS['sql_views_as_tables'])) {
2203 $dump .= $this->exportComment(
2204 __('Structure for view')
2205 . ' '
2206 . $formatted_table_name
2207 )
2208 . $this->exportComment();
2209 // delete the stand-in table previously created (if any)
2210 if ($export_type !== 'table') {
2211 $dump .= 'DROP TABLE IF EXISTS '
2212 . Util::backquote($table_alias) . ';' . $crlf;
2213 }
2214 $dump .= $this->getTableDef(
2215 $db,
2216 $table,
2217 $crlf,
2218 $error_url,
2219 $dates,
2220 true,
2221 true,
2222 true,
2223 $aliases
2224 );
2225 } else {
2226 $dump .= $this->exportComment(
2227 sprintf(
2228 __('Structure for view %s exported as a table'),
2229 $formatted_table_name
2230 )
2231 )
2232 . $this->exportComment();
2233 // delete the stand-in table previously created (if any)
2234 if ($export_type !== 'table') {
2235 $dump .= 'DROP TABLE IF EXISTS '
2236 . Util::backquote($table_alias) . ';' . $crlf;
2237 }
2238 $dump .= $this->getTableDefForView(
2239 $db,
2240 $table,
2241 $crlf,
2242 true,
2243 $aliases
2244 );
2245 }
2246 break;
2247 case 'stand_in':
2248 $dump .= $this->exportComment(
2249 __('Stand-in structure for view') . ' ' . $formatted_table_name
2250 )
2251 . $this->exportComment(
2252 __('(See below for the actual view)')
2253 )
2254 . $this->exportComment();
2255 // export a stand-in definition to resolve view dependencies
2256 $dump .= $this->getTableDefStandIn($db, $table, $crlf, $aliases);
2257 }
2258
2259 // this one is built by getTableDef() to use in table copy/move
2260 // but not in the case of export
2261 unset($GLOBALS['sql_constraints_query']);
2262
2263 return $this->export->outputHandler($dump);
2264 }
2265
2266 /**
2267 * Outputs the content of a table in SQL format
2268 *
2269 * @param string $db database name
2270 * @param string $table table name
2271 * @param string $crlf the end of line sequence
2272 * @param string $error_url the url to go back in case of error
2273 * @param string $sql_query SQL query for obtaining data
2274 * @param array $aliases Aliases of db/table/columns
2275 *
2276 * @return bool Whether it succeeded
2277 */
2278 public function exportData(
2279 $db,
2280 $table,
2281 $crlf,
2282 $error_url,
2283 $sql_query,
2284 array $aliases = []
2285 ) {
2286 global $current_row, $sql_backquotes, $dbi;
2287
2288 // Do not export data for merge tables
2289 if ($dbi->getTable($db, $table)->isMerge()) {
2290 return true;
2291 }
2292
2293 $db_alias = $db;
2294 $table_alias = $table;
2295 $this->initAlias($aliases, $db_alias, $table_alias);
2296
2297 if (isset($GLOBALS['sql_compatibility'])) {
2298 $compat = $GLOBALS['sql_compatibility'];
2299 } else {
2300 $compat = 'NONE';
2301 }
2302
2303 $formatted_table_name = Util::backquoteCompat(
2304 $table_alias,
2305 $compat,
2306 $sql_backquotes
2307 );
2308
2309 // Do not export data for a VIEW, unless asked to export the view as a table
2310 // (For a VIEW, this is called only when exporting a single VIEW)
2311 if ($dbi->getTable($db, $table)->isView()
2312 && empty($GLOBALS['sql_views_as_tables'])
2313 ) {
2314 $head = $this->possibleCRLF()
2315 . $this->exportComment()
2316 . $this->exportComment('VIEW ' . $formatted_table_name)
2317 . $this->exportComment(__('Data:') . ' ' . __('None'))
2318 . $this->exportComment()
2319 . $this->possibleCRLF();
2320
2321 return $this->export->outputHandler($head);
2322 }
2323
2324 $result = $dbi->tryQuery(
2325 $sql_query,
2326 DatabaseInterface::CONNECT_USER,
2327 DatabaseInterface::QUERY_UNBUFFERED
2328 );
2329 // a possible error: the table has crashed
2330 $tmp_error = $dbi->getError();
2331 if ($tmp_error) {
2332 $message = sprintf(__('Error reading data for table %s:'), $db . '.' . $table);
2333 $message .= ' ' . $tmp_error;
2334 if (! defined('TESTSUITE')) {
2335 trigger_error($message, E_USER_ERROR);
2336 }
2337
2338 return $this->export->outputHandler(
2339 $this->exportComment($message)
2340 );
2341 }
2342
2343 if ($result == false) {
2344 $dbi->freeResult($result);
2345
2346 return true;
2347 }
2348
2349 $fields_cnt = $dbi->numFields($result);
2350
2351 // Get field information
2352 $fields_meta = $dbi->getFieldsMeta($result);
2353 $field_flags = [];
2354 for ($j = 0; $j < $fields_cnt; $j++) {
2355 $field_flags[$j] = $dbi->fieldFlags($result, $j);
2356 }
2357
2358 $field_set = [];
2359 for ($j = 0; $j < $fields_cnt; $j++) {
2360 $col_as = $fields_meta[$j]->name;
2361 if (! empty($aliases[$db]['tables'][$table]['columns'][$col_as])) {
2362 $col_as = $aliases[$db]['tables'][$table]['columns'][$col_as];
2363 }
2364 $field_set[$j] = Util::backquoteCompat(
2365 $col_as,
2366 $compat,
2367 $sql_backquotes
2368 );
2369 }
2370
2371 if (isset($GLOBALS['sql_type'])
2372 && $GLOBALS['sql_type'] === 'UPDATE'
2373 ) {
2374 // update
2375 $schema_insert = 'UPDATE ';
2376 if (isset($GLOBALS['sql_ignore'])) {
2377 $schema_insert .= 'IGNORE ';
2378 }
2379 // avoid EOL blank
2380 $schema_insert .= Util::backquoteCompat(
2381 $table_alias,
2382 $compat,
2383 $sql_backquotes
2384 ) . ' SET';
2385 } else {
2386 // insert or replace
2387 if (isset($GLOBALS['sql_type'])
2388 && $GLOBALS['sql_type'] === 'REPLACE'
2389 ) {
2390 $sql_command = 'REPLACE';
2391 } else {
2392 $sql_command = 'INSERT';
2393 }
2394
2395 // delayed inserts?
2396 if (isset($GLOBALS['sql_delayed'])) {
2397 $insert_delayed = ' DELAYED';
2398 } else {
2399 $insert_delayed = '';
2400 }
2401
2402 // insert ignore?
2403 if (isset($GLOBALS['sql_type'], $GLOBALS['sql_ignore']) && $GLOBALS['sql_type'] === 'INSERT') {
2404 $insert_delayed .= ' IGNORE';
2405 }
2406 //truncate table before insert
2407 if (isset($GLOBALS['sql_truncate'])
2408 && $GLOBALS['sql_truncate']
2409 && $sql_command === 'INSERT'
2410 ) {
2411 $truncate = 'TRUNCATE TABLE '
2412 . Util::backquoteCompat(
2413 $table_alias,
2414 $compat,
2415 $sql_backquotes
2416 ) . ';';
2417 $truncatehead = $this->possibleCRLF()
2418 . $this->exportComment()
2419 . $this->exportComment(
2420 __('Truncate table before insert') . ' '
2421 . $formatted_table_name
2422 )
2423 . $this->exportComment()
2424 . $crlf;
2425 $this->export->outputHandler($truncatehead);
2426 $this->export->outputHandler($truncate);
2427 }
2428
2429 // scheme for inserting fields
2430 if ($GLOBALS['sql_insert_syntax'] === 'complete'
2431 || $GLOBALS['sql_insert_syntax'] === 'both'
2432 ) {
2433 $fields = implode(', ', $field_set);
2434 $schema_insert = $sql_command . $insert_delayed . ' INTO '
2435 . Util::backquoteCompat(
2436 $table_alias,
2437 $compat,
2438 $sql_backquotes
2439 )
2440 // avoid EOL blank
2441 . ' (' . $fields . ') VALUES';
2442 } else {
2443 $schema_insert = $sql_command . $insert_delayed . ' INTO '
2444 . Util::backquoteCompat(
2445 $table_alias,
2446 $compat,
2447 $sql_backquotes
2448 )
2449 . ' VALUES';
2450 }
2451 }
2452
2453 //\x08\\x09, not required
2454 $current_row = 0;
2455 $query_size = 0;
2456 if (($GLOBALS['sql_insert_syntax'] === 'extended'
2457 || $GLOBALS['sql_insert_syntax'] === 'both')
2458 && (! isset($GLOBALS['sql_type'])
2459 || $GLOBALS['sql_type'] !== 'UPDATE')
2460 ) {
2461 $separator = ',';
2462 $schema_insert .= $crlf;
2463 } else {
2464 $separator = ';';
2465 }
2466
2467 while ($row = $dbi->fetchRow($result)) {
2468 if ($current_row == 0) {
2469 $head = $this->possibleCRLF()
2470 . $this->exportComment()
2471 . $this->exportComment(
2472 __('Dumping data for table') . ' '
2473 . $formatted_table_name
2474 )
2475 . $this->exportComment()
2476 . $crlf;
2477 if (! $this->export->outputHandler($head)) {
2478 return false;
2479 }
2480 }
2481 // We need to SET IDENTITY_INSERT ON for MSSQL
2482 if (isset($GLOBALS['sql_compatibility'])
2483 && $GLOBALS['sql_compatibility'] === 'MSSQL'
2484 && $current_row == 0
2485 ) {
2486 if (! $this->export->outputHandler(
2487 'SET IDENTITY_INSERT '
2488 . Util::backquoteCompat(
2489 $table_alias,
2490 $compat,
2491 $sql_backquotes
2492 )
2493 . ' ON ;' . $crlf
2494 )
2495 ) {
2496 return false;
2497 }
2498 }
2499 $current_row++;
2500 $values = [];
2501 for ($j = 0; $j < $fields_cnt; $j++) {
2502 // NULL
2503 if (! isset($row[$j]) || $row[$j] === null) {
2504 $values[] = 'NULL';
2505 } elseif ($fields_meta[$j]->numeric
2506 && $fields_meta[$j]->type !== 'timestamp'
2507 && ! $fields_meta[$j]->blob
2508 ) {
2509 // a number
2510 // timestamp is numeric on some MySQL 4.1, BLOBs are
2511 // sometimes numeric
2512 $values[] = $row[$j];
2513 } elseif (stripos($field_flags[$j], 'BINARY') !== false
2514 && isset($GLOBALS['sql_hex_for_binary'])
2515 ) {
2516 // a true BLOB
2517 // - mysqldump only generates hex data when the --hex-blob
2518 // option is used, for fields having the binary attribute
2519 // no hex is generated
2520 // - a TEXT field returns type blob but a real blob
2521 // returns also the 'binary' flag
2522
2523 // empty blobs need to be different, but '0' is also empty
2524 // :-(
2525 if (empty($row[$j]) && $row[$j] != '0') {
2526 $values[] = '\'\'';
2527 } else {
2528 $values[] = '0x' . bin2hex($row[$j]);
2529 }
2530 } elseif ($fields_meta[$j]->type === 'bit') {
2531 // detection of 'bit' works only on mysqli extension
2532 $values[] = "b'" . $dbi->escapeString(
2533 Util::printableBitValue(
2534 (int) $row[$j],
2535 (int) $fields_meta[$j]->length
2536 )
2537 )
2538 . "'";
2539 } elseif ($fields_meta[$j]->type === 'geometry') {
2540 // export GIS types as hex
2541 $values[] = '0x' . bin2hex($row[$j]);
2542 } elseif (! empty($GLOBALS['exporting_metadata'])
2543 && $row[$j] === '@LAST_PAGE'
2544 ) {
2545 $values[] = '@LAST_PAGE';
2546 } else {
2547 // something else -> treat as a string
2548 $values[] = '\''
2549 . $dbi->escapeString($row[$j])
2550 . '\'';
2551 }
2552 }
2553
2554 // should we make update?
2555 if (isset($GLOBALS['sql_type'])
2556 && $GLOBALS['sql_type'] === 'UPDATE'
2557 ) {
2558 $insert_line = $schema_insert;
2559 for ($i = 0; $i < $fields_cnt; $i++) {
2560 if ($i == 0) {
2561 $insert_line .= ' ';
2562 }
2563 if ($i > 0) {
2564 // avoid EOL blank
2565 $insert_line .= ',';
2566 }
2567 $insert_line .= $field_set[$i] . ' = ' . $values[$i];
2568 }
2569
2570 [$tmp_unique_condition, $tmp_clause_is_unique] = Util::getUniqueCondition(
2571 $result,
2572 $fields_cnt,
2573 $fields_meta,
2574 $row
2575 );
2576 $insert_line .= ' WHERE ' . $tmp_unique_condition;
2577 unset($tmp_unique_condition, $tmp_clause_is_unique);
2578 } else {
2579 // Extended inserts case
2580 if ($GLOBALS['sql_insert_syntax'] === 'extended'
2581 || $GLOBALS['sql_insert_syntax'] === 'both'
2582 ) {
2583 if ($current_row == 1) {
2584 $insert_line = $schema_insert . '('
2585 . implode(', ', $values) . ')';
2586 } else {
2587 $insert_line = '(' . implode(', ', $values) . ')';
2588 $insertLineSize = mb_strlen($insert_line);
2589 $sql_max_size = $GLOBALS['sql_max_query_size'];
2590 if (isset($sql_max_size)
2591 && $sql_max_size > 0
2592 && $query_size + $insertLineSize > $sql_max_size
2593 ) {
2594 if (! $this->export->outputHandler(';' . $crlf)) {
2595 return false;
2596 }
2597 $query_size = 0;
2598 $current_row = 1;
2599 $insert_line = $schema_insert . $insert_line;
2600 }
2601 }
2602 $query_size += mb_strlen($insert_line);
2603 // Other inserts case
2604 } else {
2605 $insert_line = $schema_insert
2606 . '(' . implode(', ', $values) . ')';
2607 }
2608 }
2609 unset($values);
2610
2611 if (! $this->export->outputHandler(
2612 ($current_row == 1 ? '' : $separator . $crlf)
2613 . $insert_line
2614 )
2615 ) {
2616 return false;
2617 }
2618 }
2619
2620 if ($current_row > 0) {
2621 if (! $this->export->outputHandler(';' . $crlf)) {
2622 return false;
2623 }
2624 }
2625
2626 // We need to SET IDENTITY_INSERT OFF for MSSQL
2627 if (isset($GLOBALS['sql_compatibility'])
2628 && $GLOBALS['sql_compatibility'] === 'MSSQL'
2629 && $current_row > 0
2630 ) {
2631 $outputSucceeded = $this->export->outputHandler(
2632 $crlf . 'SET IDENTITY_INSERT '
2633 . Util::backquoteCompat(
2634 $table_alias,
2635 $compat,
2636 $sql_backquotes
2637 )
2638 . ' OFF;' . $crlf
2639 );
2640 if (! $outputSucceeded) {
2641 return false;
2642 }
2643 }
2644
2645 $dbi->freeResult($result);
2646
2647 return true;
2648 }
2649
2650 /**
2651 * Make a create table statement compatible with MSSQL
2652 *
2653 * @param string $create_query MySQL create table statement
2654 *
2655 * @return string MSSQL compatible create table statement
2656 */
2657 private function makeCreateTableMSSQLCompatible($create_query)
2658 {
2659 // In MSSQL
2660 // 1. No 'IF NOT EXISTS' in CREATE TABLE
2661 // 2. DATE field doesn't exists, we will use DATETIME instead
2662 // 3. UNSIGNED attribute doesn't exist
2663 // 4. No length on INT, TINYINT, SMALLINT, BIGINT and no precision on
2664 // FLOAT fields
2665 // 5. No KEY and INDEX inside CREATE TABLE
2666 // 6. DOUBLE field doesn't exists, we will use FLOAT instead
2667
2668 $create_query = (string) preg_replace(
2669 '/^CREATE TABLE IF NOT EXISTS/',
2670 'CREATE TABLE',
2671 (string) $create_query
2672 );
2673 // first we need to replace all lines ended with '" DATE ...,\n'
2674 // last preg_replace preserve us from situation with date text
2675 // inside DEFAULT field value
2676 $create_query = (string) preg_replace(
2677 "/\" date DEFAULT NULL(,)?\n/",
2678 '" datetime DEFAULT NULL$1' . "\n",
2679 $create_query
2680 );
2681 $create_query = (string) preg_replace(
2682 "/\" date NOT NULL(,)?\n/",
2683 '" datetime NOT NULL$1' . "\n",
2684 $create_query
2685 );
2686 $create_query = (string) preg_replace(
2687 '/" date NOT NULL DEFAULT \'([^\'])/',
2688 '" datetime NOT NULL DEFAULT \'$1',
2689 $create_query
2690 );
2691
2692 // next we need to replace all lines ended with ') UNSIGNED ...,'
2693 // last preg_replace preserve us from situation with unsigned text
2694 // inside DEFAULT field value
2695 $create_query = (string) preg_replace(
2696 "/\) unsigned NOT NULL(,)?\n/",
2697 ') NOT NULL$1' . "\n",
2698 $create_query
2699 );
2700 $create_query = (string) preg_replace(
2701 "/\) unsigned DEFAULT NULL(,)?\n/",
2702 ') DEFAULT NULL$1' . "\n",
2703 $create_query
2704 );
2705 $create_query = (string) preg_replace(
2706 '/\) unsigned NOT NULL DEFAULT \'([^\'])/',
2707 ') NOT NULL DEFAULT \'$1',
2708 $create_query
2709 );
2710
2711 // we need to replace all lines ended with
2712 // '" INT|TINYINT([0-9]{1,}) ...,' last preg_replace preserve us
2713 // from situation with int([0-9]{1,}) text inside DEFAULT field
2714 // value
2715 $create_query = (string) preg_replace(
2716 '/" (int|tinyint|smallint|bigint)\([0-9]+\) DEFAULT NULL(,)?\n/',
2717 '" $1 DEFAULT NULL$2' . "\n",
2718 $create_query
2719 );
2720 $create_query = (string) preg_replace(
2721 '/" (int|tinyint|smallint|bigint)\([0-9]+\) NOT NULL(,)?\n/',
2722 '" $1 NOT NULL$2' . "\n",
2723 $create_query
2724 );
2725 $create_query = (string) preg_replace(
2726 '/" (int|tinyint|smallint|bigint)\([0-9]+\) NOT NULL DEFAULT \'([^\'])/',
2727 '" $1 NOT NULL DEFAULT \'$2',
2728 $create_query
2729 );
2730
2731 // we need to replace all lines ended with
2732 // '" FLOAT|DOUBLE([0-9,]{1,}) ...,'
2733 // last preg_replace preserve us from situation with
2734 // float([0-9,]{1,}) text inside DEFAULT field value
2735 $create_query = (string) preg_replace(
2736 '/" (float|double)(\([0-9]+,[0-9,]+\))? DEFAULT NULL(,)?\n/',
2737 '" float DEFAULT NULL$3' . "\n",
2738 $create_query
2739 );
2740 $create_query = (string) preg_replace(
2741 '/" (float|double)(\([0-9,]+,[0-9,]+\))? NOT NULL(,)?\n/',
2742 '" float NOT NULL$3' . "\n",
2743 $create_query
2744 );
2745
2746 return (string) preg_replace(
2747 '/" (float|double)(\([0-9,]+,[0-9,]+\))? NOT NULL DEFAULT \'([^\'])/',
2748 '" float NOT NULL DEFAULT \'$3',
2749 $create_query
2750 );
2751
2752 // @todo remove indexes from CREATE TABLE
2753 }
2754
2755 /**
2756 * replaces db/table/column names with their aliases
2757 *
2758 * @param string $sql_query SQL query in which aliases are to be substituted
2759 * @param array $aliases Alias information for db/table/column
2760 * @param string $db the database name
2761 * @param string $table the tablename
2762 * @param string $flag the flag denoting whether any replacement was done
2763 *
2764 * @return string query replaced with aliases
2765 */
2766 public function replaceWithAliases(
2767 $sql_query,
2768 array $aliases,
2769 $db,
2770 $table = '',
2771 &$flag = null
2772 ) {
2773 $flag = false;
2774
2775 /**
2776 * The parser of this query.
2777 *
2778 * @var Parser $parser
2779 */
2780 $parser = new Parser($sql_query);
2781
2782 if (empty($parser->statements[0])) {
2783 return $sql_query;
2784 }
2785
2786 /**
2787 * The statement that represents the query.
2788 *
2789 * @var CreateStatement $statement
2790 */
2791 $statement = $parser->statements[0];
2792
2793 /**
2794 * Old database name.
2795 *
2796 * @var string $old_database
2797 */
2798 $old_database = $db;
2799
2800 // Replacing aliases in `CREATE TABLE` statement.
2801 if ($statement->options->has('TABLE')) {
2802 // Extracting the name of the old database and table from the
2803 // statement to make sure the parameters are correct.
2804 if (! empty($statement->name->database)) {
2805 $old_database = $statement->name->database;
2806 }
2807
2808 /**
2809 * Old table name.
2810 *
2811 * @var string $old_table
2812 */
2813 $old_table = $statement->name->table;
2814
2815 // Finding the aliased database name.
2816 // The database might be empty so we have to add a few checks.
2817 $new_database = null;
2818 if (! empty($statement->name->database)) {
2819 $new_database = $statement->name->database;
2820 if (! empty($aliases[$old_database]['alias'])) {
2821 $new_database = $aliases[$old_database]['alias'];
2822 }
2823 }
2824
2825 // Finding the aliases table name.
2826 $new_table = $old_table;
2827 if (! empty($aliases[$old_database]['tables'][$old_table]['alias'])) {
2828 $new_table = $aliases[$old_database]['tables'][$old_table]['alias'];
2829 }
2830
2831 // Replacing new values.
2832 if (($statement->name->database !== $new_database)
2833 || ($statement->name->table !== $new_table)
2834 ) {
2835 $statement->name->database = $new_database;
2836 $statement->name->table = $new_table;
2837 $statement->name->expr = ''; // Force rebuild.
2838 $flag = true;
2839 }
2840
2841 /** @var CreateDefinition $field */
2842 foreach ($statement->fields as $field) {
2843 // Column name.
2844 if (! empty($field->type)) {
2845 if (! empty($aliases[$old_database]['tables'][$old_table]['columns'][$field->name])) {
2846 $field->name = $aliases[$old_database]['tables'][$old_table]['columns'][$field->name];
2847 $flag = true;
2848 }
2849 }
2850
2851 // Key's columns.
2852 if (! empty($field->key)) {
2853 foreach ($field->key->columns as $key => $column) {
2854 if (empty($aliases[$old_database]['tables'][$old_table]['columns'][$column['name']])) {
2855 continue;
2856 }
2857
2858 $columnAliases = $aliases[$old_database]['tables'][$old_table]['columns'];
2859 $field->key->columns[$key]['name'] = $columnAliases[$column['name']];
2860 $flag = true;
2861 }
2862 }
2863
2864 // References.
2865 if (empty($field->references)) {
2866 continue;
2867 }
2868
2869 $ref_table = $field->references->table->table;
2870 // Replacing table.
2871 if (! empty($aliases[$old_database]['tables'][$ref_table]['alias'])) {
2872 $field->references->table->table
2873 = $aliases[$old_database]['tables'][$ref_table]['alias'];
2874 $field->references->table->expr = '';
2875 $flag = true;
2876 }
2877 // Replacing column names.
2878 foreach ($field->references->columns as $key => $column) {
2879 if (empty($aliases[$old_database]['tables'][$ref_table]['columns'][$column])) {
2880 continue;
2881 }
2882
2883 $field->references->columns[$key]
2884 = $aliases[$old_database]['tables'][$ref_table]['columns'][$column];
2885 $flag = true;
2886 }
2887 }
2888 } elseif ($statement->options->has('TRIGGER')) {
2889 // Extracting the name of the old database and table from the
2890 // statement to make sure the parameters are correct.
2891 if (! empty($statement->table->database)) {
2892 $old_database = $statement->table->database;
2893 }
2894
2895 /**
2896 * Old table name.
2897 *
2898 * @var string $old_table
2899 */
2900 $old_table = $statement->table->table;
2901
2902 if (! empty($aliases[$old_database]['tables'][$old_table]['alias'])) {
2903 $statement->table->table
2904 = $aliases[$old_database]['tables'][$old_table]['alias'];
2905 $statement->table->expr = ''; // Force rebuild.
2906 $flag = true;
2907 }
2908 }
2909
2910 if ($statement->options->has('TRIGGER')
2911 || $statement->options->has('PROCEDURE')
2912 || $statement->options->has('FUNCTION')
2913 || $statement->options->has('VIEW')
2914 ) {
2915 // Replacing the body.
2916 for ($i = 0, $count = count((array) $statement->body); $i < $count; ++$i) {
2917
2918 /**
2919 * Token parsed at this moment.
2920 *
2921 * @var Token $token
2922 */
2923 $token = $statement->body[$i];
2924
2925 // Replacing only symbols (that are not variables) and unknown
2926 // identifiers.
2927 $isSymbol = $token->type === Token::TYPE_SYMBOL;
2928 $isKeyword = $token->type === Token::TYPE_KEYWORD;
2929 $isNone = $token->type === Token::TYPE_NONE;
2930 $replaceToken = $isSymbol
2931 && (! ($token->flags & Token::FLAG_SYMBOL_VARIABLE))
2932 || ($isKeyword
2933 && (! ($token->flags & Token::FLAG_KEYWORD_RESERVED))
2934 || $isNone);
2935
2936 if (! $replaceToken) {
2937 continue;
2938 }
2939
2940 $alias = $this->getAlias($aliases, $token->value);
2941 if (empty($alias)) {
2942 continue;
2943 }
2944
2945 // Replacing the token.
2946 $token->token = Context::escape($alias);
2947 $flag = true;
2948 }
2949 }
2950
2951 return $statement->build();
2952 }
2953
2954 /**
2955 * Generate comment
2956 *
2957 * @param string $crlf Carriage return character
2958 * @param string|null $sql_statement SQL statement
2959 * @param string $comment1 Comment for dumped table
2960 * @param string $comment2 Comment for current table
2961 * @param string $table_alias Table alias
2962 * @param string $compat Compatibility mode
2963 *
2964 * @return string
2965 */
2966 protected function generateComment(
2967 $crlf,
2968 ?string $sql_statement,
2969 $comment1,
2970 $comment2,
2971 $table_alias,
2972 $compat
2973 ) {
2974 if (! isset($sql_statement)) {
2975 if (isset($GLOBALS['no_constraints_comments'])) {
2976 $sql_statement = '';
2977 } else {
2978 $sql_statement = $crlf
2979 . $this->exportComment()
2980 . $this->exportComment($comment1)
2981 . $this->exportComment();
2982 }
2983 }
2984
2985 // comments for current table
2986 if (! isset($GLOBALS['no_constraints_comments'])) {
2987 $sql_statement .= $crlf
2988 . $this->exportComment()
2989 . $this->exportComment(
2990 $comment2 . ' ' . Util::backquoteCompat(
2991 $table_alias,
2992 $compat,
2993 isset($GLOBALS['sql_backquotes'])
2994 )
2995 )
2996 . $this->exportComment();
2997 }
2998
2999 return $sql_statement;
3000 }
3001 }
5.0.4-english_vs_5.1.0-english.