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