TYPO3 3.7.0: typo3_src-3.7.0/typo3/ext/phpmyadmin/modsub/phpmyadmin-2.5.6-rc1/libraries/export/sql.php Source File

00001 <?php
00002 /* $Id: sql.php,v 2.6 2004/01/02 16:11:56 rabus Exp $ */
00003 // vim: expandtab sw=4 ts=4 sts=4:
00004 error_reporting(E_ALL);
00023 function PMA_fieldTypes($db, $table,$use_backquotes) {
00024     PMA_mysql_select_db($db);
00025     $table_def = PMA_mysql_query('SHOW FIELDS FROM ' . PMA_backquote($db) . '.' . PMA_backquote($table));
00026     while($row = @PMA_mysql_fetch_array($table_def)) {
00027         $types[PMA_backquote($row['Field'],$use_backquotes)] = ereg_replace('\\(.*', '', $row['Type']);
00028     }
00029     return $types;
00030 }
00031 
00039 function PMA_exportComment($text) {
00040     return PMA_exportOutputHandler('# ' . $text . $GLOBALS['crlf']);
00041 }
00042 
00050 function PMA_exportHeader() {
00051     global $crlf;
00052     global $cfg;
00053 
00054     $head  =  '# phpMyAdmin SQL Dump' . $crlf
00055            .  '# version ' . PMA_VERSION . $crlf
00056            .  '# http://www.phpmyadmin.net' . $crlf
00057            .  '#' . $crlf
00058            .  '# ' . $GLOBALS['strHost'] . ': ' . $cfg['Server']['host'];
00059     if (!empty($cfg['Server']['port'])) {
00060          $head .= ':' . $cfg['Server']['port'];
00061     }
00062     $head .= $crlf
00063            .  '# ' . $GLOBALS['strGenTime'] . ': ' . PMA_localisedDate() . $crlf
00064            .  '# ' . $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
00065            .  '# ' . $GLOBALS['strPHPVersion'] . ': ' . phpversion() . $crlf;
00066     return PMA_exportOutputHandler($head);
00067 }
00068 
00078 function PMA_exportDBCreate($db) {
00079     global $crlf;
00080     if (isset($GLOBALS['drop_database'])) {
00081         if (!PMA_exportOutputHandler('DROP DATABASE ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : $db) . ';' . $crlf)) return FALSE;
00082     }
00083     $create_query = 'CREATE DATABASE ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : $db);
00084     if (PMA_MYSQL_INT_VERSION >= 40101) {
00085         $collation = PMA_getDbCollation($db);
00086         if (strpos($collation, '_')) {
00087             $create_query .= ' DEFAULT CHARACTER SET ' . substr($collation, 0, strpos($collation, '_')) . ' COLLATE ' . $collation;
00088         } else {
00089             $create_query .= ' DEFAULT CHARACTER SET ' . $collation;
00090         }
00091     }
00092     $create_query .= ';' . $crlf;
00093     if (!PMA_exportOutputHandler($create_query)) return FALSE;
00094     return PMA_exportOutputHandler('USE ' . $db . ';' . $crlf);
00095 }
00096 
00106 function PMA_exportDBHeader($db) {
00107     global $crlf;
00108     $head = '# ' . $crlf
00109           . '# ' . $GLOBALS['strDatabase'] . ': ' . (isset($GLOBALS['use_backquotes']) ? PMA_backquote($db) : '\'' . $db . '\''). $crlf
00110           . '# ' . $crlf;
00111     return PMA_exportOutputHandler($head);
00112 }
00113 
00123 function PMA_exportDBFooter($db) {
00124     if (isset($GLOBALS['sql_constraints'])) return PMA_exportOutputHandler($GLOBALS['sql_constraints']);
00125     return TRUE;
00126 }
00127 
00145 function PMA_getTableDef($db, $table, $crlf, $error_url, $show_dates = false)
00146 {
00147     global $drop;
00148     global $use_backquotes;
00149     global $cfgRelation;
00150     global $sql_constraints;
00151 
00152     $schema_create = '';
00153     $auto_increment = '';
00154     $new_crlf = $crlf;
00155 
00156 
00157     $result = PMA_mysql_query('SHOW TABLE STATUS FROM ' . PMA_backquote($db) . ' LIKE \'' . PMA_sqlAddslashes($table) . '\'');
00158     if ($result != FALSE) {
00159         if (mysql_num_rows($result) > 0) {
00160             $tmpres        = PMA_mysql_fetch_array($result);
00161             if (isset($GLOBALS['auto_increment']) && !empty($tmpres['Auto_increment'])) {
00162                 $auto_increment .= ' AUTO_INCREMENT=' . $tmpres['Auto_increment'] . ' ';
00163             }
00164 
00165             if ($show_dates && isset($tmpres['Create_time']) && !empty($tmpres['Create_time'])) {
00166                 $schema_create .= '# ' . $GLOBALS['strStatCreateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Create_time'])) . $crlf;
00167                 $new_crlf = '#' . $crlf . $crlf;
00168             }
00169 
00170             if ($show_dates && isset($tmpres['Update_time']) && !empty($tmpres['Update_time'])) {
00171                 $schema_create .= '# ' . $GLOBALS['strStatUpdateTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Update_time'])) . $crlf;
00172                 $new_crlf = '#' . $crlf . $crlf;
00173             }
00174 
00175             if ($show_dates && isset($tmpres['Check_time']) && !empty($tmpres['Check_time'])) {
00176                 $schema_create .= '# ' . $GLOBALS['strStatCheckTime'] . ': ' . PMA_localisedDate(strtotime($tmpres['Check_time'])) . $crlf;
00177                 $new_crlf = '#' . $crlf . $crlf;
00178             }
00179         mysql_free_result($result);
00180         }
00181     }
00182 
00183     $schema_create .= $new_crlf;
00184 
00185     if (!empty($drop)) {
00186         $schema_create .= 'DROP TABLE IF EXISTS ' . PMA_backquote($table, $use_backquotes) . ';' . $crlf;
00187     }
00188 
00189     // Steve Alberty's patch for complete table dump,
00190     // Whether to quote table and fields names or not
00191     if ($use_backquotes) {
00192         PMA_mysql_query('SET SQL_QUOTE_SHOW_CREATE = 1');
00193     } else {
00194         PMA_mysql_query('SET SQL_QUOTE_SHOW_CREATE = 0');
00195     }
00196     $result = PMA_mysql_query('SHOW CREATE TABLE ' . PMA_backquote($db) . '.' . PMA_backquote($table));
00197     if ($result != FALSE && mysql_num_rows($result) > 0) {
00198         $tmpres        = PMA_mysql_fetch_array($result);
00199         // Fix for case problems with winwin, thanks to
00200         // Pawe³ Szczepañski <pauluz at users.sourceforge.net>
00201         $pos           = strpos($tmpres[1], ' (');
00202 
00203         // Fix a problem with older versions of mysql
00204         // Find the first opening parenthesys, i.e. that after the name
00205         // of the table
00206         $pos2          = strpos($tmpres[1], '(');
00207         // Old mysql did not insert a space after table name
00208         // in query "show create table ..."!
00209         if ($pos2 != $pos + 1)
00210         {
00211             // This is the real position of the first character after
00212             // the name of the table
00213             $pos = $pos2;
00214             // Old mysql did not even put newlines and indentation...
00215             $tmpres[1] = str_replace(",", ",\n     ", $tmpres[1]);
00216         }
00217 
00218         $tmpres[1]     = substr($tmpres[1], 0, 13)
00219                        . (($use_backquotes) ? PMA_backquote($tmpres[0]) : $tmpres[0])
00220                        . substr($tmpres[1], $pos);
00221         $tmpres[1]     = str_replace("\n", $crlf, $tmpres[1]);
00222         if (preg_match_all('((,\r?\n[\s]*(CONSTRAINT|FOREIGN[\s]*KEY)[^\r\n,]+)+)', $tmpres[1], $regs)) {
00223             if (!isset($sql_constraints)) {
00224                 if (isset($GLOBALS['no_constraints_comments'])) {
00225                     $sql_constraints = '';
00226                 } else {
00227                     $sql_constraints = $crlf . '#' . $crlf
00228                                         . '# ' . $GLOBALS['strConstraintsForDumped'] . $crlf
00229                                         . '#' . $crlf;
00230                 }
00231             }
00232             if (!isset($GLOBALS['no_constraints_comments'])) {
00233                 $sql_constraints .= $crlf .'#' . $crlf .'# ' . $GLOBALS['strConstraintsForTable'] . ' ' . PMA_backquote($table) . $crlf . '#' . $crlf;
00234             }
00235             $sql_constraints .= 'ALTER TABLE ' . PMA_backquote($table) . $crlf
00236                              . preg_replace('/(,\r?\n|^)([\s]*)(CONSTRAINT|FOREIGN[\s]*KEY)/', '\1\2ADD \3', substr($regs[0][0], 2))
00237                             . ";\n";
00238             $tmpres[1]     = preg_replace('((,\r?\n[\s]*(CONSTRAINT|FOREIGN[\s]*KEY)[^\r\n,]+)+)', '', $tmpres[1]);
00239         }
00240         $schema_create .= $tmpres[1];
00241     }
00242 
00243     $schema_create .= $auto_increment;
00244 
00245 
00246     mysql_free_result($result);
00247     return $schema_create;
00248 } // end of the 'PMA_getTableDef()' function
00249 
00250 
00265 function PMA_getTableComments($db, $table, $crlf, $do_relation = false, $do_comments = false, $do_mime = false)
00266 {
00267     global $cfgRelation;
00268     global $use_backquotes;
00269     global $sql_constraints;
00270 
00271     $schema_create = '';
00272 
00273     if ($do_comments && $cfgRelation['commwork']) {
00274         if (!($comments_map = PMA_getComments($db, $table))) unset($comments_map);
00275     }
00276 
00277     // Check if we can use Relations (Mike Beck)
00278     if ($do_relation && !empty($cfgRelation['relation'])) {
00279         // Find which tables are related with the current one and write it in
00280         // an array
00281         $res_rel = PMA_getForeigners($db, $table);
00282 
00283         if ($res_rel && count($res_rel) > 0) {
00284             $have_rel = TRUE;
00285         } else {
00286             $have_rel = FALSE;
00287         }
00288     }
00289     else {
00290            $have_rel = FALSE;
00291     } // end if
00292 
00293     if ($do_mime && $cfgRelation['mimework']) {
00294         if (!($mime_map = PMA_getMIME($db, $table, true))) unset($mime_map);
00295     }
00296 
00297     if (isset($comments_map) && count($comments_map) > 0) {
00298         $schema_create .= $crlf . '#' . $crlf . '# COMMENTS FOR TABLE ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf;
00299         foreach($comments_map AS $comment_field => $comment) {
00300             $schema_create .= '#   ' . PMA_backquote($comment_field, $use_backquotes) . $crlf . '#       ' . PMA_backquote($comment, $use_backquotes) . $crlf;
00301         }
00302         $schema_create .= '#' . $crlf;
00303     }
00304 
00305     if (isset($mime_map) && count($mime_map) > 0) {
00306         $schema_create .= $crlf . '#' . $crlf . '# MIME TYPES FOR TABLE ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf;
00307         @reset($mime_map);
00308         foreach($mime_map AS $mime_field => $mime) {
00309             $schema_create .= '#   ' . PMA_backquote($mime_field, $use_backquotes) . $crlf . '#       ' . PMA_backquote($mime['mimetype'], $use_backquotes) . $crlf;
00310         }
00311         $schema_create .= '#' . $crlf;
00312     }
00313 
00314     if ($have_rel) {
00315         $schema_create .= $crlf . '#' . $crlf . '# RELATIONS FOR TABLE ' . PMA_backquote($table, $use_backquotes) . ':' . $crlf;
00316         foreach($res_rel AS $rel_field => $rel) {
00317             $schema_create .= '#   ' . PMA_backquote($rel_field, $use_backquotes) . $crlf . '#       ' . PMA_backquote($rel['foreign_table'], $use_backquotes) . ' -> ' . PMA_backquote($rel['foreign_field'], $use_backquotes) . $crlf;
00318         }
00319         $schema_create .= '#' . $crlf;
00320     }
00321 
00322     return $schema_create;
00323 
00324 } // end of the 'PMA_getTableComments()' function
00325 
00341 function PMA_exportStructure($db, $table, $crlf, $error_url, $relation = FALSE, $comments = FALSE, $mime = FALSE, $dates = FALSE) {
00342     $formatted_table_name = (isset($GLOBALS['use_backquotes']))
00343                           ? PMA_backquote($table)
00344                           : '\'' . $table . '\'';
00345     $dump = $crlf
00346           . '# --------------------------------------------------------' . $crlf
00347           .  $crlf . '#' . $crlf
00348           .  '# ' . $GLOBALS['strTableStructure'] . ' ' . $formatted_table_name . $crlf
00349           .  '#' . $crlf
00350           .  PMA_getTableDef($db, $table, $crlf, $error_url, $dates) . ';' . $crlf
00351           .  PMA_getTableComments($db, $table, $crlf, $relation, $comments, $mime);
00352 
00353 
00354     return PMA_exportOutputHandler($dump);
00355 }
00356 
00380 function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
00381 {
00382     global $use_backquotes;
00383     global $rows_cnt;
00384     global $current_row;
00385 
00386     $formatted_table_name = (isset($GLOBALS['use_backquotes']))
00387                           ? PMA_backquote($table)
00388                           : '\'' . $table . '\'';
00389     $head = $crlf
00390           . '#' . $crlf
00391           . '# ' . $GLOBALS['strDumpingData'] . ' ' . $formatted_table_name . $crlf
00392           . '#' . $crlf .$crlf;
00393 
00394     if (!PMA_exportOutputHandler($head)) return FALSE;
00395 
00396     $buffer = '';
00397 
00398     $result      = PMA_mysql_query($sql_query) or PMA_mysqlDie('', $sql_query, '', $error_url);
00399     if ($result != FALSE) {
00400         $fields_cnt = mysql_num_fields($result);
00401         $rows_cnt   = mysql_num_rows($result);
00402 
00403         // get the real types of the table's fields (in an array)
00404         // the key of the array is the backquoted field name
00405         $field_types = PMA_fieldTypes($db,$table,$use_backquotes);
00406 
00407         // analyze the query to get the true column names, not the aliases
00408         // (this fixes an undefined index, also if Complete inserts
00409         //  are used, we did not get the true column name in case of aliases)
00410         $analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
00411 
00412         // Checks whether the field is an integer or not
00413         for ($j = 0; $j < $fields_cnt; $j++) {
00414 
00415             if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
00416                 $field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $use_backquotes);
00417             } else {
00418                 $field_set[$j] = PMA_backquote(PMA_mysql_field_name($result, $j), $use_backquotes);
00419             }
00420 
00421             $type          = $field_types[$field_set[$j]];
00422 
00423             if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' ||
00424                 $type == 'bigint'  || (PMA_MYSQL_INT_VERSION < 40100 && $type == 'timestamp')) {
00425                 $field_num[$j] = TRUE;
00426             } else {
00427                 $field_num[$j] = FALSE;
00428             }
00429             // blob
00430             if ($type == 'blob' || $type == 'mediumblob' || $type == 'longblob' || $type == 'tinyblob') {
00431                 $field_blob[$j] = TRUE;
00432             } else {
00433                 $field_blob[$j] = FALSE;
00434             }
00435         } // end for
00436 
00437         if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'update') {
00438             // update
00439             $schema_insert  = 'UPDATE ' . PMA_backquote($table, $use_backquotes) . ' SET ';
00440             $fields_no      = count($field_set);
00441         } else {
00442             // insert or replace
00443             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'replace') {
00444                 $sql_command    = 'REPLACE';
00445             } else {
00446                 $sql_command    = 'INSERT';
00447             }
00448 
00449             // delayed inserts?
00450             if (isset($GLOBALS['delayed'])) {
00451                 $insert_delayed = ' DELAYED';
00452             } else {
00453                 $insert_delayed = '';
00454             }
00455 
00456             // Sets the scheme
00457             if (isset($GLOBALS['showcolumns'])) {
00458                 $fields        = implode(', ', $field_set);
00459                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $use_backquotes)
00460                                . ' (' . $fields . ') VALUES (';
00461             } else {
00462                 $schema_insert = $sql_command . $insert_delayed .' INTO ' . PMA_backquote($table, $use_backquotes)
00463                                . ' VALUES (';
00464             }
00465         }
00466 
00467         $search       = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
00468         $replace      = array('\0', '\n', '\r', '\Z');
00469         $current_row  = 0;
00470 
00471         while ($row = PMA_mysql_fetch_row($result)) {
00472             $current_row++;
00473             for ($j = 0; $j < $fields_cnt; $j++) {
00474                 if (!isset($row[$j])) {
00475                     $values[]     = 'NULL';
00476                 } else if ($row[$j] == '0' || $row[$j] != '') {
00477                     // a number
00478                     if ($field_num[$j]) {
00479                         $values[] = $row[$j];
00480                     // a not empty blob
00481                     } else if ($field_blob[$j] && !empty($row[$j])) {
00482                         $values[] = '0x' . bin2hex($row[$j]);
00483                     // a string
00484                     } else {
00485                         $values[] = "'" . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . "'";
00486                     }
00487                 } else {
00488                     $values[]     = "''";
00489                 } // end if
00490             } // end for
00491 
00492             // should we make update?
00493             if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'update') {
00494 
00495                 $insert_line = $schema_insert;
00496                 for ($i = 0; $i < $fields_no; $i++) {
00497                     if ($i > 0) {
00498                         $insert_line .= ', ';
00499                     }
00500                     $insert_line .= $field_set[$i] . ' = ' . $values[$i];
00501                 }
00502 
00503             } else {
00504 
00505                 // Extended inserts case
00506                 if (isset($GLOBALS['extended_ins'])) {
00507                     if ($current_row == 1) {
00508                         $insert_line  = $schema_insert . implode(', ', $values) . ')';
00509                     } else {
00510                         $insert_line  = '(' . implode(', ', $values) . ')';
00511                     }
00512                 }
00513                 // Other inserts case
00514                 else {
00515                     $insert_line      = $schema_insert . implode(', ', $values) . ')';
00516                 }
00517             }
00518             unset($values);
00519 
00520             if (!PMA_exportOutputHandler($insert_line . ((isset($GLOBALS['extended_ins']) && ($current_row < $rows_cnt)) ? ',' : ';') . $crlf)) return FALSE;
00521 
00522         } // end while
00523     } // end if ($result != FALSE)
00524     mysql_free_result($result);
00525 
00526     return TRUE;
00527 } // end of the 'PMA_exportData()' function
00528 ?>
Documentation TYPO3 par Ameos

sql.php
