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