Documentation TYPO3 par Ameos |
00001 <?php 00002 /* $Id: sql.php,v 2.45 2005/06/24 12:01:43 nijel Exp $ */ 00003 // vim: expandtab sw=4 ts=4 sts=4: 00011 $GLOBALS['comment_marker'] = '-- '; 00012 00020 function PMA_exportComment($text) { 00021 return PMA_exportOutputHandler($GLOBALS['comment_marker'] . $text . $GLOBALS['crlf']); 00022 } 00023 00031 function PMA_exportFooter() { 00032 global $crlf; 00033 00034 $foot = ''; 00035 00036 if (isset($GLOBALS['disable_fk'])) { 00037 $foot .= $crlf . 'SET FOREIGN_KEY_CHECKS=1;' . $crlf; 00038 } 00039 00040 if (isset($GLOBALS['use_transaction'])) { 00041 $foot .= $crlf . 'COMMIT;' . $crlf; 00042 } 00043 00044 return PMA_exportOutputHandler($foot); 00045 } 00046 00054 function PMA_exportHeader() { 00055 global $crlf; 00056 global $cfg; 00057 00058 if (PMA_MYSQL_INT_VERSION >= 40100 && isset($GLOBALS['sql_compat']) && $GLOBALS['sql_compat'] != 'NONE') { 00059 PMA_DBI_try_query('SET SQL_MODE="' . $GLOBALS['sql_compat'] . '"'); 00060 } 00061 00062 $head = $GLOBALS['comment_marker'] . 'phpMyAdmin SQL Dump' . $crlf 00063 . $GLOBALS['comment_marker'] . 'version ' . PMA_VERSION . $crlf 00064 . $GLOBALS['comment_marker'] . 'http://www.phpmyadmin.net' . $crlf 00065 . $GLOBALS['comment_marker'] . $crlf 00066 . $GLOBALS['comment_marker'] . $GLOBALS['strHost'] . ': ' . $cfg['Server']['host']; 00067 if (!empty($cfg['Server']['port'])) { 00068 $head .= ':' . $cfg['Server']['port']; 00069 } 00070 $head .= $crlf 00071 . $GLOBALS['comment_marker'] . $GLOBALS['strGenTime'] . ': ' . PMA_localisedDate() . $crlf 00072 . $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 00073 . $GLOBALS['comment_marker'] . $GLOBALS['strPHPVersion'] . ': ' . phpversion() . $crlf; 00074 00075 if (isset($GLOBALS['header_comment']) && !empty($GLOBALS['header_comment'])) { 00076 $lines = explode('\n', $GLOBALS['header_comment']); 00077 $head .= $GLOBALS['comment_marker'] . $crlf 00078 . $GLOBALS['comment_marker'] . implode($crlf . $GLOBALS['comment_marker'], $lines) . $crlf 00079 . $GLOBALS['comment_marker'] . $crlf; 00080 } 00081 00082 if (isset($GLOBALS['disable_fk'])) { 00083 $head .= $crlf . 'SET FOREIGN_KEY_CHECKS=0;' . $crlf; 00084 } 00085 00086 if (isset($GLOBALS['use_transaction'])) { 00087 $head .= $crlf .'SET AUTOCOMMIT=0;' . $crlf 00088 . 'START TRANSACTION;' . $crlf . $crlf; 00089 } 00090 00091 return PMA_exportOutputHandler($head); 00092 } 00093 00103 function PMA_exportDBCreate($db) { 00104 global $crlf; 00105 if (isset($GLOBALS['drop_database'])) { 00106 if (!PMA_exportOutputHandler('DROP DATABASE ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : $db) . ';' . $crlf)) return FALSE; 00107 } 00108 $create_query = 'CREATE DATABASE ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : $db); 00109 if (PMA_MYSQL_INT_VERSION >= 40101) { 00110 $collation = PMA_getDbCollation($db); 00111 if (strpos($collation, '_')) { 00112 $create_query .= ' DEFAULT CHARACTER SET ' . substr($collation, 0, strpos($collation, '_')) . ' COLLATE ' . $collation; 00113 } else { 00114 $create_query .= ' DEFAULT CHARACTER SET ' . $collation; 00115 } 00116 } 00117 $create_query .= ';' . $crlf; 00118 if (!PMA_exportOutputHandler($create_query)) return FALSE; 00119 return PMA_exportOutputHandler('USE ' . $db . ';' . $crlf); 00120 } 00121 00131 function PMA_exportDBHeader($db) { 00132 global $crlf; 00133 $head = $GLOBALS['comment_marker'] . $crlf 00134 . $GLOBALS['comment_marker'] . $GLOBALS['strDatabase'] . ': ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : '\'' . $db . '\''). $crlf 00135 . $GLOBALS['comment_marker'] . $crlf; 00136 return PMA_exportOutputHandler($head); 00137 } 00138 00148 function PMA_exportDBFooter($db) { 00149 $result = TRUE; 00150 if (isset($GLOBALS['sql_constraints'])) { 00151 $result = PMA_exportOutputHandler($GLOBALS['sql_constraints']); 00152 unset($GLOBALS['sql_constraints']); 00153 } 00154 return $result; 00155 } 00156 00174 function PMA_getTableDef($db, $table, $crlf, $error_url, $show_dates = false) 00175 { 00176 global $drop; 00177 global $use_backquotes; 00178 global $cfgRelation; 00179 global $sql_constraints; 00180 00181 $schema_create = ''; 00182 $auto_increment = ''; 00183 $new_crlf = $crlf; 00184 00185 // need to use PMA_DBI_QUERY_STORE with PMA_DBI_num_rows() in mysqli 00186 $result = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . PMA_sqlAddslashes($table) . '\'', NULL, PMA_DBI_QUERY_STORE); 00187 if ($result != FALSE) { 00188 if (PMA_DBI_num_rows($result) > 0) { 00189 $tmpres = PMA_DBI_fetch_assoc($result); 00190 if (isset($GLOBALS['auto_increment']) && !empty($tmpres['Auto_increment'])) { 00191 $auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' '; 00192 } 00193 00194 if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) { 00195 $schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatCreateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Create_time'])) . $crlf; 00196 $new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf; 00197 } 00198 00199 if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) { 00200 $schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatUpdateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Update_time'])) . $crlf; 00201 $new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf; 00202 } 00203 00204 if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) { 00205 $schema_create .= $GLOBALS['comment_marker'] . $GLOBALS['strStatCheckTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Check_time'])) . $crlf; 00206 $new_crlf = $GLOBALS['comment_marker'] . $crlf . $crlf; 00207 } 00208 } 00209 PMA_DBI_free_result($result); 00210 } 00211 00212 $schema_create .= $new_crlf; 00213 00214 if (!empty($drop)) { 00215 $schema_create .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table, $use_backquotes) . ';' . $crlf; 00216 } 00217 00218 // Steve Alberty's patch for complete table dump, 00219 // Whether to quote table and fields names or not 00220 if ($use_backquotes) { 00221 PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 1'); 00222 } else { 00223 PMA_DBI_query('SET SQL_QUOTE_SHOW_CREATE = 0'); 00224 } 00225 00226 $result = PMA_DBI_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table), NULL, PMA_DBI_QUERY_UNBUFFERED); 00227 if ($result != FALSE && ($row = PMA_DBI_fetch_row($result))) { 00228 $create_query = $row[1]; 00229 unset($row); 00230 00231 // Convert end of line chars to one that we want (note that MySQL doesn't return query it will accept in all cases) 00232 if (strpos($create_query, "(\r\n ")) { 00233 $create_query = str_replace("\r\n", $crlf, $create_query); 00234 } elseif (strpos($create_query, "(\n ")) { 00235 $create_query = str_replace("\n", $crlf, $create_query); 00236 } elseif (strpos($create_query, "(\r ")) { 00237 $create_query = str_replace("\r", $crlf, $create_query); 00238 } 00239 00240 // Should we use IF NOT EXISTS? 00241 if (isset($GLOBALS['if_not_exists'])) { 00242 $create_query = preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query); 00243 } 00244 00245 // are there any constraints to cut out? 00246 if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $create_query)) { 00247 00248 // Split the query into lines, so we can easily handle it. We know lines are separated by $crlf (done few lines above). 00249 $sql_lines = explode($crlf, $create_query); 00250 $sql_count = count($sql_lines); 00251 00252 // lets find first line with constraints 00253 for ($i = 0; $i < $sql_count; $i++) { 00254 if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $sql_lines[$i])) break; 00255 } 00256 00257 // remove , from the end of create statement 00258 $sql_lines[$i - 1] = preg_replace('@,$@', '', $sql_lines[$i - 1]); 00259 00260 // prepare variable for constraints 00261 if (!isset($sql_constraints)) { 00262 if (isset($GLOBALS['no_constraints_comments'])) { 00263 $sql_constraints = ''; 00264 } else { 00265 $sql_constraints = $crlf . $GLOBALS['comment_marker'] . 00266 $crlf . $GLOBALS['comment_marker'] . $GLOBALS['strConstraintsForDumped'] . 00267 $crlf . $GLOBALS['comment_marker'] . $crlf; 00268 } 00269 } 00270 00271 // comments for current table 00272 if (!isset($GLOBALS['no_constraints_comments'])) { 00273 $sql_constraints .= $crlf . $GLOBALS['comment_marker'] . 00274 $crlf . $GLOBALS['comment_marker'] . $GLOBALS['strConstraintsForTable'] . ' ' . PMA_backquote($table) . 00275 $crlf . $GLOBALS['comment_marker'] . $crlf; 00276 } 00277 00278 // let's do the work 00279 $sql_constraints .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf; 00280 00281 $first = TRUE; 00282 for($j = $i; $j < $sql_count; $j++) { 00283 if (preg_match('@CONSTRAINT|FOREIGN[\s]+KEY@', $sql_lines[$j])) { 00284 if (!$first) { 00285 $sql_constraints .= $crlf; 00286 } 00287 if (strpos($sql_lines[$j], 'CONSTRAINT') === FALSE) { 00288 $sql_constraints .= preg_replace('/(FOREIGN[\s]+KEY)/', 'ADD \1', $sql_lines[$j]); 00289 } else { 00290 $sql_constraints .= preg_replace('/(CONSTRAINT)/', 'ADD \1', $sql_lines[$j]); 00291 } 00292 $first = FALSE; 00293 } else { 00294 break; 00295 } 00296 } 00297 $sql_constraints .= ';' . $crlf; 00298 $create_query = implode($crlf, array_slice($sql_lines, 0, $i)) . $crlf . implode($crlf, array_slice($sql_lines, $j, $sql_count - 1)); 00299 unset($sql_lines); 00300 } 00301 $schema_create .= $create_query; 00302 } 00303 00304 $schema_create .= $auto_increment; 00305 00306 PMA_DBI_free_result($result); 00307 return $schema_create; 00308 } // end of the 'PMA_getTableDef()' function 00309 00310 00325 function PMA_getTableComments($db, $table, $crlf, $do_relation = false, $do_comments = false, $do_mime = false) 00326 { 00327 global $cfgRelation; 00328 global $use_backquotes; 00329 global $sql_constraints; 00330 00331 $schema_create = ''; 00332 00333 // triggered only for MySQL < 4.1.x (pmadb-style comments) 00334 if ($do_comments && $cfgRelation['commwork']) { 00335 if (!($comments_map = PMA_getComments($db, $table))) { 00336 unset($comments_map); 00337 } 00338 } 00339 00340 // Check if we can use Relations (Mike Beck) 00341 if ($do_relation && !empty($cfgRelation['relation'])) { 00342 // Find which tables are related with the current one and write it in 00343 // an array 00344 $res_rel = PMA_getForeigners($db, $table); 00345 00346 if ($res_rel && count($res_rel) > 0) { 00347 $have_rel = TRUE; 00348 } else { 00349 $have_rel = FALSE; 00350 } 00351 } 00352 else { 00353 $have_rel = FALSE; 00354 } // end if 00355 00356 if ($do_mime && $cfgRelation['mimework']) { 00357 if (!($mime_map = PMA_getMIME($db, $table, true))) unset($mime_map); 00358 } 00359 00360 if (isset($comments_map) && count($comments_map) > 0) { 00361 $schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf 00362 . $GLOBALS['comment_marker'] . $GLOBALS['strCommentsForTable']. ' ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf; 00363 foreach ($comments_map AS $comment_field => $comment) { 00364 $schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($comment_field, $use_backquotes) . $crlf 00365 . $GLOBALS['comment_marker'] . ' ' . PMA_backquote($comment, $use_backquotes) . $crlf; 00366 } 00367 $schema_create .= $GLOBALS['comment_marker'] . $crlf; 00368 } 00369 00370 if (isset($mime_map) && count($mime_map) > 0) { 00371 $schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf 00372 . $GLOBALS['comment_marker'] . $GLOBALS['strMIMETypesForTable']. ' ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf; 00373 @reset($mime_map); 00374 foreach ($mime_map AS $mime_field => $mime) { 00375 $schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($mime_field, $use_backquotes) . $crlf 00376 . $GLOBALS['comment_marker'] . ' ' . PMA_backquote($mime['mimetype'], $use_backquotes) . $crlf; 00377 } 00378 $schema_create .= $GLOBALS['comment_marker'] . $crlf; 00379 } 00380 00381 if ($have_rel) { 00382 $schema_create .= $crlf . $GLOBALS['comment_marker'] . $crlf 00383 . $GLOBALS['comment_marker'] . $GLOBALS['strRelationsForTable']. ' ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf; 00384 foreach ($res_rel AS $rel_field => $rel) { 00385 $schema_create .= $GLOBALS['comment_marker'] . ' ' . PMA_backquote($rel_field, $use_backquotes) . $crlf 00386 . $GLOBALS['comment_marker'] . ' ' . PMA_backquote($rel['foreign_table'], $use_backquotes) 00387 . ' -> ' . PMA_backquote($rel['foreign_field'], $use_backquotes) . $crlf; 00388 } 00389 $schema_create .= $GLOBALS['comment_marker'] . $crlf; 00390 } 00391 00392 return $schema_create; 00393 00394 } // end of the 'PMA_getTableComments()' function 00395 00411 function PMA_exportStructure($db, $table, $crlf, $error_url, $relation = FALSE, $comments = FALSE, $mime = FALSE, $dates = FALSE) { 00412 $formatted_table_name = (isset($GLOBALS['use_backquotes'])) 00413 ? PMA_backquote($table) 00414 : '\'' . $table . '\''; 00415 $dump = $crlf 00416 . $GLOBALS['comment_marker'] . '--------------------------------------------------------' . $crlf 00417 . $crlf . $GLOBALS['comment_marker'] . $crlf 00418 . $GLOBALS['comment_marker'] . $GLOBALS['strTableStructure'] . ' ' . $formatted_table_name . $crlf 00419 . $GLOBALS['comment_marker'] . $crlf 00420 . PMA_getTableDef($db, $table, $crlf, $error_url, $dates) . ';' . $crlf 00421 . PMA_getTableComments($db, $table, $crlf, $relation, $comments, $mime); 00422 00423 00424 return PMA_exportOutputHandler($dump); 00425 } 00426 00450 function PMA_exportData($db, $table, $crlf, $error_url, $sql_query) 00451 { 00452 global $use_backquotes; 00453 global $rows_cnt; 00454 global $current_row; 00455 00456 $formatted_table_name = (isset($GLOBALS['use_backquotes'])) 00457 ? PMA_backquote($table) 00458 : '\'' . $table . '\''; 00459 $head = $crlf 00460 . $GLOBALS['comment_marker'] . $crlf 00461 . $GLOBALS['comment_marker'] . $GLOBALS['strDumpingData'] . ' ' . $formatted_table_name . $crlf 00462 . $GLOBALS['comment_marker'] . $crlf .$crlf; 00463 00464 if (!PMA_exportOutputHandler($head)) return FALSE; 00465 00466 $buffer = ''; 00467 00468 // analyze the query to get the true column names, not the aliases 00469 // (this fixes an undefined index, also if Complete inserts 00470 // are used, we did not get the true column name in case of aliases) 00471 $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query)); 00472 00473 $result = PMA_DBI_query($sql_query, NULL, PMA_DBI_QUERY_UNBUFFERED); 00474 if ($result != FALSE) { 00475 $fields_cnt = PMA_DBI_num_fields($result); 00476 00477 // Get field information 00478 $fields_meta = PMA_DBI_get_fields_meta($result); 00479 $field_flags = array(); 00480 for ($j = 0; $j < $fields_cnt; $j++) { 00481 $field_flags[$j] = PMA_DBI_field_flags($result, $j); 00482 } 00483 00484 for ($j = 0; $j < $fields_cnt; $j++) { 00485 if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) { 00486 $field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $use_backquotes); 00487 } else { 00488 $field_set[$j] = PMA_backquote($fields_meta[$j]->name, $use_backquotes); 00489 } 00490 } 00491 00492 if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'update') { 00493 // update 00494 $schema_insert = 'UPDATE '; 00495 if (isset($GLOBALS['sql_ignore'])) 00496 $schema_insert .= 'IGNORE '; 00497 $schema_insert .= PMA_backquote($table, $use_backquotes) . ' SET '; 00498 } else { 00499 // insert or replace 00500 if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'replace') { 00501 $sql_command = 'REPLACE'; 00502 } else { 00503 $sql_command = 'INSERT'; 00504 } 00505 00506 // delayed inserts? 00507 if (isset($GLOBALS['delayed'])) { 00508 $insert_delayed = ' DELAYED'; 00509 } else { 00510 $insert_delayed = ''; 00511 } 00512 00513 // insert ignore? 00514 if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'insert' && isset($GLOBALS['sql_ignore'])) { 00515 $insert_delayed .= ' IGNORE'; 00516 } 00517 00518 // scheme for inserting fields 00519 if (isset($GLOBALS['showcolumns'])) { 00520 $fields = implode(', ', $field_set); 00521 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $use_backquotes) 00522 . ' (' . $fields . ') VALUES ('; 00523 } else { 00524 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $use_backquotes) 00525 . ' VALUES ('; 00526 } 00527 } 00528 00529 $search = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required 00530 $replace = array('\0', '\n', '\r', '\Z'); 00531 $current_row = 0; 00532 $separator = isset($GLOBALS['extended_ins']) ? ',' : ';'; 00533 00534 while ($row = PMA_DBI_fetch_row($result)) { 00535 $current_row++; 00536 for ($j = 0; $j < $fields_cnt; $j++) { 00537 // NULL 00538 if (!isset($row[$j]) || is_null($row[$j])) { 00539 $values[] = 'NULL'; 00540 // a number 00541 // timestamp is numeric on some MySQL 4.1 00542 } elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp') { 00543 $values[] = $row[$j]; 00544 // a binary field 00545 // Note: with mysqli, under MySQL 4.1.3, we get the flag 00546 // "binary" for those field types (I don't know why) 00547 } else if (stristr($field_flags[$j], 'BINARY') 00548 && isset($GLOBALS['hexforbinary']) 00549 && $fields_meta[$j]->type != 'datetime' 00550 && $fields_meta[$j]->type != 'date' 00551 && $fields_meta[$j]->type != 'time' 00552 && $fields_meta[$j]->type != 'timestamp' 00553 ) { 00554 // empty blobs need to be different, but '0' is also empty :-( 00555 if (empty($row[$j]) && $row[$j] != '0') { 00556 $values[] = '\'\''; 00557 } else { 00558 $values[] = '0x' . bin2hex($row[$j]); 00559 } 00560 // something else -> treat as a string 00561 } else { 00562 $values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\''; 00563 } // end if 00564 } // end for 00565 00566 // should we make update? 00567 if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'update') { 00568 00569 $insert_line = $schema_insert; 00570 for ($i = 0; $i < $fields_cnt; $i++) { 00571 if ($i > 0) { 00572 $insert_line .= ', '; 00573 } 00574 $insert_line .= $field_set[$i] . ' = ' . $values[$i]; 00575 } 00576 00577 $insert_line .= ' WHERE ' . PMA_getUvaCondition($result, $fields_cnt, $fields_meta, $row); 00578 00579 } else { 00580 00581 // Extended inserts case 00582 if (isset($GLOBALS['extended_ins'])) { 00583 if ($current_row == 1) { 00584 $insert_line = $schema_insert . implode(', ', $values) . ')'; 00585 } else { 00586 $insert_line = '(' . implode(', ', $values) . ')'; 00587 } 00588 } 00589 // Other inserts case 00590 else { 00591 $insert_line = $schema_insert . implode(', ', $values) . ')'; 00592 } 00593 } 00594 unset($values); 00595 00596 if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) return FALSE; 00597 00598 } // end while 00599 if ($current_row > 0) { 00600 if (!PMA_exportOutputHandler(';' . $crlf)) return FALSE; 00601 } 00602 } // end if ($result != FALSE) 00603 PMA_DBI_free_result($result); 00604 00605 return TRUE; 00606 } // end of the 'PMA_exportData()' function 00607 ?>