Rev Author Line No. Line
250 kaklik 1 <?php
2 /* $Id: database_interface.lib.php,v 2.39.2.2 2006/03/08 17:54:29 lem9 Exp $ */
3 // vim: expandtab sw=4 ts=4 sts=4:
4  
5 /**
6 * Common Option Constants For DBI Functions
7 */
8 // PMA_DBI_try_query()
9 define('PMA_DBI_QUERY_STORE', 1); // Force STORE_RESULT method, ignored by classic MySQL.
10 define('PMA_DBI_QUERY_UNBUFFERED', 2); // Do not read whole query
11 // PMA_DBI_get_variable()
12 define('PMA_DBI_GETVAR_SESSION', 1);
13 define('PMA_DBI_GETVAR_GLOBAL', 2);
14  
15 /**
16 * Loads the mysql extensions if it is not loaded yet
17 *
18 * @param string $extension mysql extension to load
19 */
20 function PMA_DBI_checkAndLoadMysqlExtension( $extension = 'mysql' ) {
21 if ( ! function_exists( $extension . '_connect' ) ) {
22 PMA_dl( $extension );
23 // check whether mysql is available
24 if ( ! function_exists( $extension . '_connect' ) ) {
25 return false;
26 }
27 }
28  
29 return true;
30 }
31  
32  
33 /**
34 * check for requested extension
35 */
36 if ( ! PMA_DBI_checkAndLoadMysqlExtension( $GLOBALS['cfg']['Server']['extension'] ) ) {
37  
38 // if it fails try alternative extension ...
39 // and display an error ...
40  
41 // TODO 2.7.1: add different messages for alternativ extension
42 // and complete fail (no alternativ extension too)
43 $GLOBALS['PMA_errors'][] =
44 sprintf( PMA_sanitize( $GLOBALS['strCantLoad'] ),
45 $GLOBALS['cfg']['Server']['extension'] )
46 .' - <a href="./Documentation.html#faqmysql" target="documentation">'
47 .$GLOBALS['strDocu'] . '</a>';
48  
49 if ( $GLOBALS['cfg']['Server']['extension'] === 'mysql' ) {
50 $alternativ_extension = 'mysqli';
51 } else {
52 $alternativ_extension = 'mysql';
53 }
54  
55 if ( ! PMA_DBI_checkAndLoadMysqlExtension( $alternativ_extension ) ) {
56 // if alternativ fails too ...
57 header( 'Location: error.php'
58 . '?lang=' . urlencode( $available_languages[$lang][2] )
59 . '&char=' . urlencode( $charset )
60 . '&dir=' . urlencode( $text_dir )
61 . '&type=' . urlencode( $strError )
62 . '&error=' . urlencode(
63 sprintf( $GLOBALS['strCantLoad'],
64 $GLOBALS['cfg']['Server']['extension'] )
65 .' - [a@./Documentation.html#faqmysql@documentation]'
66 .$GLOBALS['strDocu'] . '[/a]' )
67 . '&' . SID
68 );
69 exit();
70 }
71  
72 $GLOBALS['cfg']['Server']['extension'] = $alternativ_extension;
73 unset( $alternativ_extension );
74 }
75  
76 /**
77 * Including The DBI Plugin
78 */
79 require_once('./libraries/dbi/' . $GLOBALS['cfg']['Server']['extension'] . '.dbi.lib.php');
80  
81 /**
82 * Common Functions
83 */
84 function PMA_DBI_query($query, $link = null, $options = 0) {
85 $res = PMA_DBI_try_query($query, $link, $options)
86 or PMA_mysqlDie(PMA_DBI_getError($link), $query);
87 return $res;
88 }
89  
90 /**
91 * converts charset of a mysql message, usally coming from mysql_error(),
92 * into PMA charset, usally UTF-8
93 * uses language to charset mapping from mysql/share/errmsg.txt
94 * and charset names to ISO charset from information_schema.CHARACTER_SETS
95 *
96 * @uses $GLOBALS['cfg']['IconvExtraParams']
97 * @uses $GLOBALS['charset'] as target charset
98 * @uses PMA_DBI_fetch_value() to get server_language
99 * @uses preg_match() to filter server_language
100 * @uses in_array()
101 * @uses function_exists() to check for a convert function
102 * @uses iconv() to convert message
103 * @uses libiconv() to convert message
104 * @uses recode_string() to convert message
105 * @uses mb_convert_encoding() to convert message
106 * @param string $message
107 * @return string $message
108 */
109 function PMA_DBI_convert_message( $message ) {
110 // latin always last!
111 $encodings = array(
112 'japanese' => 'EUC-JP', //'ujis',
113 'japanese-sjis' => 'Shift-JIS', //'sjis',
114 'korean' => 'EUC-KR', //'euckr',
115 'russian' => 'KOI8-R', //'koi8r',
116 'ukrainian' => 'KOI8-U', //'koi8u',
117 'greek' => 'ISO-8859-7', //'greek',
118 'serbian' => 'CP1250', //'cp1250',
119 'estonian' => 'ISO-8859-13', //'latin7',
120 'slovak' => 'ISO-8859-2', //'latin2',
121 'czech' => 'ISO-8859-2', //'latin2',
122 'hungarian' => 'ISO-8859-2', //'latin2',
123 'polish' => 'ISO-8859-2', //'latin2',
124 'romanian' => 'ISO-8859-2', //'latin2',
125 'spanish' => 'CP1252', //'latin1',
126 'swedish' => 'CP1252', //'latin1',
127 'italian' => 'CP1252', //'latin1',
128 'norwegian-ny' => 'CP1252', //'latin1',
129 'norwegian' => 'CP1252', //'latin1',
130 'portuguese' => 'CP1252', //'latin1',
131 'danish' => 'CP1252', //'latin1',
132 'dutch' => 'CP1252', //'latin1',
133 'english' => 'CP1252', //'latin1',
134 'french' => 'CP1252', //'latin1',
135 'german' => 'CP1252', //'latin1',
136 );
137  
138 if ( $server_language = PMA_DBI_fetch_value( 'SHOW VARIABLES LIKE \'language\';', 0, 1 ) ) {
139 $found = array();
140 if ( preg_match( '&(?:\\\|\\/)([^\\\\\/]*)(?:\\\|\\/)$&i', $server_language, $found )) {
141 $server_language = $found[1];
142 }
143 }
144  
145 if ( ! empty( $server_language ) && isset( $encodings[$server_language] ) ) {
146 if ( function_exists( 'iconv' ) ) {
147 $message = iconv( $encodings[$server_language],
148 $GLOBALS['charset'] . $GLOBALS['cfg']['IconvExtraParams'], $message);
149 } elseif ( function_exists( 'recode_string' ) ) {
150 $message = recode_string( $encodings[$server_language] . '..' . $GLOBALS['charset'],
151 $message );
152 } elseif ( function_exists( 'libiconv' ) ) {
153 $message = libiconv( $encodings[$server_language], $GLOBALS['charset'], $message );
154 } elseif ( function_exists( 'mb_convert_encoding' ) ) {
155 // do not try unsupported charsets
156 if ( ! in_array( $server_language, array( 'ukrainian', 'greek', 'serbian' ) ) ) {
157 $message = mb_convert_encoding( $message, $GLOBALS['charset'],
158 $encodings[$server_language] );
159 }
160 }
161 } else {
162 // lang not found, try all
163 // what TODO ?
164 }
165  
166 return $message;
167 }
168  
169 /**
170 * returns array with database names
171 *
172 * @return array $databases
173 */
174 function PMA_DBI_get_dblist($link = null)
175 {
176 $dbs_array = PMA_DBI_fetch_result('SHOW DATABASES;', $link);
177  
178 // Before MySQL 4.0.2, SHOW DATABASES could send the
179 // whole list, so check if we really have access:
180 if (PMA_MYSQL_INT_VERSION < 40002 || !empty($GLOBALS['cfg']['Server']['hide_db'])) {
181 foreach ($dbs_array as $key => $db) {
182 if (!@PMA_DBI_select_db($db, $link) || (!empty($GLOBALS['cfg']['Server']['hide_db']) && preg_match('/' . $GLOBALS['cfg']['Server']['hide_db'] . '/', $db))) {
183 unset( $dbs_array[$key] );
184 }
185 }
186 // re-index values
187 $dbs_array = array_values( $dbs_array );
188 }
189  
190 return $dbs_array;
191 }
192  
193 /**
194 * returns array with table names for given db
195 *
196 * @param string $database name of database
197 * @param mixed $link mysql link resource|object
198 * @return array tables names
199 */
200 function PMA_DBI_get_tables($database, $link = null)
201 {
202 return PMA_DBI_fetch_result('SHOW TABLES FROM ' . PMA_backquote($database) . ';',
203 null, 0, $link, PMA_DBI_QUERY_STORE);
204 }
205  
206 /**
207 * returns array of all tables in given db or dbs
208 * this function expects unqoted names:
209 * RIGHT: my_database
210 * WRONG: `my_database`
211 * WRONG: my\_database
212 * if $tbl_is_group is true, $table is used as filter for table names
213 * if $tbl_is_group is 'comment, $table is used as filter for table comments
214 *
215 * <code>
216 * PMA_DBI_get_tables_full( 'my_database' );
217 * PMA_DBI_get_tables_full( 'my_database', 'my_table' ) );
218 * PMA_DBI_get_tables_full( 'my_database', 'my_tables_', true ) );
219 * PMA_DBI_get_tables_full( 'my_database', 'my_tables_', 'comment' ) );
220 * </code>
221 *
222 * @uses PMA_MYSQL_INT_VERSION
223 * @uses PMA_DBI_fetch_result()
224 * @uses PMA_escape_mysql_wildcards()
225 * @uses PMA_backquote()
226 * @uses is_array()
227 * @uses addslashes()
228 * @uses strpos()
229 * @uses strtoupper()
230 * @param string $databases database
231 * @param string $table table
232 * @param boolean|string $tbl_is_group $table is a table group
233 * @param resource $link mysql link
234 * @return array list of tbales in given db(s)
235 */
236 function PMA_DBI_get_tables_full($database, $table = false,
237 $tbl_is_group = false, $link = null)
238 {
239 // prepare and check parameters
240 if ( ! is_array($database) ) {
241 $databases = array(addslashes($database));
242 } else {
243 $databases = array_map('addslashes', $database);
244 }
245  
246 $tables = array();
247  
248 if ( PMA_MYSQL_INT_VERSION >= 50002 ) {
249 // get table information from information_schema
250 if ( $table ) {
251 if ( true === $tbl_is_group ) {
252 $sql_where_table = 'AND `TABLE_NAME` LIKE \''
253 . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
254 } elseif ( 'comment' === $tbl_is_group ) {
255 $sql_where_table = 'AND `TABLE_COMMENT` LIKE \''
256 . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
257 } else {
258 $sql_where_table = 'AND `TABLE_NAME` = \'' . addslashes($table) . '\'';
259 }
260 } else {
261 $sql_where_table = '';
262 }
263  
264 // for PMA bc:
265 // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
266 $sql = '
267 SELECT *,
268 `TABLE_SCHEMA` AS `Db`,
269 `TABLE_NAME` AS `Name`,
270 `ENGINE` AS `Engine`,
271 `ENGINE` AS `Type`,
272 `VERSION` AS `Version`,
273 `ROW_FORMAT` AS `Row_format`,
274 `TABLE_ROWS` AS `Rows`,
275 `AVG_ROW_LENGTH` AS `Avg_row_length`,
276 `DATA_LENGTH` AS `Data_length`,
277 `MAX_DATA_LENGTH` AS `Max_data_length`,
278 `INDEX_LENGTH` AS `Index_length`,
279 `DATA_FREE` AS `Data_free`,
280 `AUTO_INCREMENT` AS `Auto_increment`,
281 `CREATE_TIME` AS `Create_time`,
282 `UPDATE_TIME` AS `Update_time`,
283 `CHECK_TIME` AS `Check_time`,
284 `TABLE_COLLATION` AS `Collation`,
285 `CHECKSUM` AS `Checksum`,
286 `CREATE_OPTIONS` AS `Create_options`,
287 `TABLE_COMMENT` AS `Comment`
288 FROM `information_schema`.`TABLES`
289 WHERE `TABLE_SCHEMA` IN (\'' . implode("', '", $databases) . '\')
290 ' . $sql_where_table;
291 $tables = PMA_DBI_fetch_result($sql, array('TABLE_SCHEMA', 'TABLE_NAME'),
292 null, $link);
293 unset( $sql_where_table, $sql );
294 } else {
295 foreach ( $databases as $each_database ) {
296 if ( true === $tbl_is_group ) {
297 $sql = 'SHOW TABLE STATUS FROM '
298 . PMA_backquote($each_database)
299 .' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';
300 } else {
301 $sql = 'SHOW TABLE STATUS FROM '
302 . PMA_backquote($each_database) . ';';
303 }
304 $each_tables = PMA_DBI_fetch_result($sql, 'Name', null, $link);
305 foreach ( $each_tables as $table_name => $each_table ) {
306 if ( 'comment' === $tbl_is_group
307 && 0 === strpos($each_table['Comment'], $table) )
308 {
309 // remove table from list
310 unset( $each_tables[$table_name] );
311 continue;
312 }
313  
314 if ( ! isset( $each_tables[$table_name]['Type'] )
315 && isset( $each_tables[$table_name]['Engine'] ) ) {
316 // pma BC, same parts of PMA still uses 'Type'
317 $each_tables[$table_name]['Type']
318 =& $each_tables[$table_name]['Engine'];
319 } elseif ( ! isset( $each_tables[$table_name]['Engine'] )
320 && isset( $each_tables[$table_name]['Type'] ) ) {
321 // old MySQL reports Type, newer MySQL reports Engine
322 $each_tables[$table_name]['Engine']
323 =& $each_tables[$table_name]['Type'];
324 }
325  
326 // MySQL forward compatibility
327 // so pma could use this array as if every server is of version >5.0
328 $each_tables[$table_name]['TABLE_SCHEMA'] = $each_database;
329 $each_tables[$table_name]['TABLE_NAME'] =& $each_tables[$table_name]['Name'];
330 $each_tables[$table_name]['ENGINE'] =& $each_tables[$table_name]['Engine'];
331 $each_tables[$table_name]['VERSION'] =& $each_tables[$table_name]['Version'];
332 $each_tables[$table_name]['ROW_FORMAT'] =& $each_tables[$table_name]['Row_format'];
333 $each_tables[$table_name]['TABLE_ROWS'] =& $each_tables[$table_name]['Rows'];
334 $each_tables[$table_name]['AVG_ROW_LENGTH'] =& $each_tables[$table_name]['Avg_row_length'];
335 $each_tables[$table_name]['DATA_LENGTH'] =& $each_tables[$table_name]['Data_length'];
336 $each_tables[$table_name]['MAX_DATA_LENGTH'] =& $each_tables[$table_name]['Max_data_length'];
337 $each_tables[$table_name]['INDEX_LENGTH'] =& $each_tables[$table_name]['Index_length'];
338 $each_tables[$table_name]['DATA_FREE'] =& $each_tables[$table_name]['Data_free'];
339 $each_tables[$table_name]['AUTO_INCREMENT'] =& $each_tables[$table_name]['Auto_increment'];
340 $each_tables[$table_name]['CREATE_TIME'] =& $each_tables[$table_name]['Create_time'];
341 $each_tables[$table_name]['UPDATE_TIME'] =& $each_tables[$table_name]['Update_time'];
342 $each_tables[$table_name]['CHECK_TIME'] =& $each_tables[$table_name]['Check_time'];
343 $each_tables[$table_name]['TABLE_COLLATION'] =& $each_tables[$table_name]['Collation'];
344 $each_tables[$table_name]['CHECKSUM'] =& $each_tables[$table_name]['Checksum'];
345 $each_tables[$table_name]['CREATE_OPTIONS'] =& $each_tables[$table_name]['Create_options'];
346 $each_tables[$table_name]['TABLE_COMMENT'] =& $each_tables[$table_name]['Comment'];
347  
348 if ( strtoupper( $each_tables[$table_name]['Comment'] ) === 'VIEW' ) {
349 $each_tables[$table_name]['TABLE_TYPE'] = 'VIEW';
350 } else {
351 // TODO difference between 'TEMPORARY' and 'BASE TABLE'
352 // but how to detect?
353 $each_tables[$table_name]['TABLE_TYPE'] = 'BASE TABLE';
354 }
355 }
356  
357 $tables[$each_database] = $each_tables;
358 }
359 }
360  
361 if ( $GLOBALS['cfg']['NaturalOrder'] ) {
362 foreach ( $tables as $key => $val ) {
363 uksort($tables[$key], 'strnatcasecmp');
364 }
365 }
366  
367 if (! is_array($database)) {
368 if (isset($tables[$database])) {
369 return $tables[$database];
370 } elseif (isset($tables[strtolower($database)])) {
371 // on windows with lower_case_table_names = 1
372 // MySQL returns
373 // with SHOW DATABASES or information_schema.SCHEMATA: `Test`
374 // but information_schema.TABLES gives `test`
375 // bug #1436171
376 // sf.net/tracker/?func=detail&aid=1436171&group_id=23067&atid=377408
377 return $tables[strtolower($database)];
378 } else {
379 return $tables;
380 }
381 } else {
382 return $tables;
383 }
384 }
385  
386 /**
387 * returns array with databases containing extended infos about them
388 *
389 * @param string $databases database
390 * @param boolean $force_stats retrieve stats also for MySQL < 5
391 * @param resource $link mysql link
392 * @return array $databases
393 */
394 function PMA_DBI_get_databases_full( $database = null, $force_stats = false, $link = null ) {
395  
396 // initialize to avoid errors when there are no databases
397 $databases = array();
398  
399 if ( PMA_MYSQL_INT_VERSION >= 50002 ) {
400 // get table information from information_schema
401 if ( $database ) {
402 $sql_where_schema = 'WHERE `SCHEMA_NAME` LIKE \''
403 . addslashes( $database ) . '\'';
404 } else {
405 $sql_where_schema = '';
406 }
407  
408 // for PMA bc:
409 // `SCHEMA_FIELD_NAME` AS `SHOW_TABLE_STATUS_FIELD_NAME`
410 $sql = '
411 SELECT `information_schema`.`SCHEMATA`.*,
412 COUNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`)
413 AS `SCHEMA_TABLES`,
414 SUM(`information_schema`.`TABLES`.`TABLE_ROWS`)
415 AS `SCHEMA_TABLE_ROWS`,
416 SUM(`information_schema`.`TABLES`.`DATA_LENGTH`)
417 AS `SCHEMA_DATA_LENGTH`,
418 SUM(`information_schema`.`TABLES`.`MAX_DATA_LENGTH`)
419 AS `SCHEMA_MAX_DATA_LENGTH`,
420 SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`)
421 AS `SCHEMA_INDEX_LENGTH`,
422 SUM(`information_schema`.`TABLES`.`DATA_LENGTH`
423 + `information_schema`.`TABLES`.`INDEX_LENGTH`)
424 AS `SCHEMA_LENGTH`,
425 SUM(`information_schema`.`TABLES`.`DATA_FREE`)
426 AS `SCHEMA_DATA_FREE`
427 FROM `information_schema`.`SCHEMATA`
428 LEFT JOIN `information_schema`.`TABLES`
429 ON `information_schema`.`TABLES`.`TABLE_SCHEMA`
430 = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`
431 ' . $sql_where_schema . '
432 GROUP BY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`';
433 $databases = PMA_DBI_fetch_result( $sql, 'SCHEMA_NAME', null, $link );
434 unset( $sql_where_schema, $sql );
435 } else {
436 foreach ( PMA_DBI_get_dblist( $link ) as $database_name ) {
437 // MySQL forward compatibility
438 // so pma could use this array as if every server is of version >5.0
439 $databases[$database_name]['SCHEMA_NAME'] = $database_name;
440  
441 if ( $force_stats ) {
442 require_once 'mysql_charsets.lib.php';
443  
444 $databases[$database_name]['DEFAULT_COLLATION_NAME']
445 = PMA_getDbCollation( $database_name );
446  
447 // get additonal info about tables
448 $databases[$database_name]['SCHEMA_TABLES'] = 0;
449 $databases[$database_name]['SCHEMA_TABLE_ROWS'] = 0;
450 $databases[$database_name]['SCHEMA_DATA_LENGTH'] = 0;
451 $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH'] = 0;
452 $databases[$database_name]['SCHEMA_INDEX_LENGTH'] = 0;
453 $databases[$database_name]['SCHEMA_LENGTH'] = 0;
454 $databases[$database_name]['SCHEMA_DATA_FREE'] = 0;
455  
456 $res = PMA_DBI_query('SHOW TABLE STATUS FROM ' . PMA_backquote( $database_name ) . ';');
457 while ( $row = PMA_DBI_fetch_assoc( $res ) ) {
458 $databases[$database_name]['SCHEMA_TABLES']++;
459 $databases[$database_name]['SCHEMA_TABLE_ROWS']
460 += $row['Rows'];
461 $databases[$database_name]['SCHEMA_DATA_LENGTH']
462 += $row['Data_length'];
463 $databases[$database_name]['SCHEMA_MAX_DATA_LENGTH']
464 += $row['Max_data_length'];
465 $databases[$database_name]['SCHEMA_INDEX_LENGTH']
466 += $row['Index_length'];
467 $databases[$database_name]['SCHEMA_DATA_FREE']
468 += $row['Data_free'];
469 $databases[$database_name]['SCHEMA_LENGTH']
470 += $row['Data_length'] + $row['Index_length'];
471 }
472 PMA_DBI_free_result( $res );
473 unset( $res );
474 }
475 }
476 }
477  
478 if ( $GLOBALS['cfg']['NaturalOrder'] ) {
479 uksort( $databases, 'strnatcasecmp' );
480 }
481  
482 return $databases;
483 }
484  
485 /**
486 * returns detailed array with all columns for given table in database,
487 * or all tables/databases
488 *
489 * @param string $database name of database
490 * @param string $table name of table to retrieve columns from
491 * @param string $column name of specific column
492 * @param mixed $link mysql link resource
493 */
494 function PMA_DBI_get_columns_full($database = null, $table = null,
495 $column = null, $link = null)
496 {
497 $columns = array();
498  
499 if ( PMA_MYSQL_INT_VERSION >= 50002 ) {
500 $sql_wheres = array();
501 $array_keys = array();
502  
503 // get columns information from information_schema
504 if ( null !== $database ) {
505 $sql_wheres[] = '`TABLE_SCHEMA` = \'' . addslashes($database) . '\' ';
506 } else {
507 $array_keys[] = 'TABLE_SCHEMA';
508 }
509 if ( null !== $table ) {
510 $sql_wheres[] = '`TABLE_NAME` = \'' . addslashes($table) . '\' ';
511 } else {
512 $array_keys[] = 'TABLE_NAME';
513 }
514 if ( null !== $column ) {
515 $sql_wheres[] = '`COLUMN_NAME` = \'' . addslashes($column) . '\' ';
516 } else {
517 $array_keys[] = 'COLUMN_NAME';
518 }
519  
520 // for PMA bc:
521 // `[SCHEMA_FIELD_NAME]` AS `[SHOW_FULL_COLUMNS_FIELD_NAME]`
522 $sql = '
523 SELECT *,
524 `COLUMN_NAME` AS `Field`,
525 `COLUMN_TYPE` AS `Type`,
526 `COLLATION_NAME` AS `Collation`,
527 `IS_NULLABLE` AS `Null`,
528 `COLUMN_KEY` AS `Key`,
529 `COLUMN_DEFAULT` AS `Default`,
530 `EXTRA` AS `Extra`,
531 `PRIVILEGES` AS `Privileges`,
532 `COLUMN_COMMENT` AS `Comment`
533 FROM `information_schema`.`COLUMNS`';
534 if ( count($sql_wheres) ) {
535 $sql .= "\n" . ' WHERE ' . implode(' AND ', $sql_wheres);
536 }
537  
538 $columns = PMA_DBI_fetch_result($sql, $array_keys, null, $link);
539 unset( $sql_wheres, $sql );
540 } else {
541 if ( null === $database ) {
542 $databases = PMA_DBI_get_dblist();
543 foreach ( $databases as $database ) {
544 $columns[$database] = PMA_DBI_get_columns_full($database, null,
545 null, $link);
546 }
547 return $columns;
548 } elseif ( null === $table ) {
549 $tables = PMA_DBI_get_tables($database);
550 foreach ( $tables as $table ) {
551 $columns[$table] = PMA_DBI_get_columns_full(
552 $database, $table, null, $link);
553 }
554 return $columns;
555 }
556  
557 $sql = 'SHOW FULL COLUMNS FROM '
558 . PMA_backquote($database) . '.' . PMA_backquote($table);
559 if ( null !== $column ) {
560 $sql .= " LIKE '" . $column . "'";
561 }
562  
563 $columns = PMA_DBI_fetch_result( $sql, 'Field', null, $link );
564  
565 $ordinal_position = 1;
566 foreach ( $columns as $column_name => $each_column ) {
567  
568 // MySQL forward compatibility
569 // so pma could use this array as if every server is of version >5.0
570 $columns[$column_name]['COLUMN_NAME'] =& $columns[$column_name]['Field'];
571 $columns[$column_name]['COLUMN_TYPE'] =& $columns[$column_name]['Type'];
572 $columns[$column_name]['COLLATION_NAME'] =& $columns[$column_name]['Collation'];
573 $columns[$column_name]['IS_NULLABLE'] =& $columns[$column_name]['Null'];
574 $columns[$column_name]['COLUMN_KEY'] =& $columns[$column_name]['Key'];
575 $columns[$column_name]['COLUMN_DEFAULT'] =& $columns[$column_name]['Default'];
576 $columns[$column_name]['EXTRA'] =& $columns[$column_name]['Extra'];
577 $columns[$column_name]['PRIVILEGES'] =& $columns[$column_name]['Privileges'];
578 $columns[$column_name]['COLUMN_COMMENT'] =& $columns[$column_name]['Comment'];
579  
580 $columns[$column_name]['TABLE_CATALOG'] = null;
581 $columns[$column_name]['TABLE_SCHEMA'] = $database;
582 $columns[$column_name]['TABLE_NAME'] = $table;
583 $columns[$column_name]['ORDINAL_POSITION'] = $ordinal_position;
584 $columns[$column_name]['DATA_TYPE'] =
585 substr($columns[$column_name]['COLUMN_TYPE'], 0,
586 strpos($columns[$column_name]['COLUMN_TYPE'], '('));
587 // @TODO guess CHARACTER_MAXIMUM_LENGTH from COLUMN_TYPE
588 $columns[$column_name]['CHARACTER_MAXIMUM_LENGTH'] = null;
589 // @TODO guess CHARACTER_OCTET_LENGTH from CHARACTER_MAXIMUM_LENGTH
590 $columns[$column_name]['CHARACTER_OCTET_LENGTH'] = null;
591 $columns[$column_name]['NUMERIC_PRECISION'] = null;
592 $columns[$column_name]['NUMERIC_SCALE'] = null;
593 $columns[$column_name]['CHARACTER_SET_NAME'] =
594 substr($columns[$column_name]['COLLATION_NAME'], 0,
595 strpos($columns[$column_name]['COLLATION_NAME'], '_'));
596  
597 $ordinal_position++;
598 }
599  
600 if ( null !== $column ) {
601 reset($columns);
602 $columns = current($columns);
603 }
604 }
605  
606 return $columns;
607 }
608  
609 /**
610 * @TODO should only return columns names, for more info use PMA_DBI_get_columns_full()
611 *
612 * @param string $database name of database
613 * @param string $table name of table to retrieve columns from
614 * @param mixed $link mysql link resource
615 */
616 function PMA_DBI_get_fields($database, $table, $link = null)
617 {
618 // here we use a try_query because when coming from
619 // tbl_create + tbl_properties.inc.php, the table does not exist
620 $fields = PMA_DBI_fetch_result(
621 'SHOW FULL COLUMNS
622 FROM ' . PMA_backquote($database) . '.' . PMA_backquote($table),
623 null, null, $link);
624 if ( ! is_array($fields) || count($fields) < 1 ) {
625 return false;
626 }
627 return $fields;
628 }
629  
630 /**
631 * returns value of given mysql server variable
632 *
633 * @param string $var mysql server variable name
634 * @param int $type PMA_DBI_GETVAR_SESSION|PMA_DBI_GETVAR_GLOBAL
635 * @param mixed $link mysql link resource|object
636 * @return mixed value for mysql server variable
637 */
638 function PMA_DBI_get_variable($var, $type = PMA_DBI_GETVAR_SESSION, $link = null)
639 {
640 if ($link === null) {
641 if (isset($GLOBALS['userlink'])) {
642 $link = $GLOBALS['userlink'];
643 } else {
644 return false;
645 }
646 }
647 if (PMA_MYSQL_INT_VERSION < 40002) {
648 $type = 0;
649 }
650 switch ($type) {
651 case PMA_DBI_GETVAR_SESSION:
652 $modifier = ' SESSION';
653 break;
654 case PMA_DBI_GETVAR_GLOBAL:
655 $modifier = ' GLOBAL';
656 break;
657 default:
658 $modifier = '';
659 }
660 return PMA_DBI_fetch_value(
661 'SHOW' . $modifier . ' VARIABLES LIKE \'' . $var . '\';', 0, 1, $link);
662 }
663  
664 /**
665 * @uses ./libraries/charset_conversion.lib.php
666 * @uses PMA_DBI_QUERY_STORE
667 * @uses PMA_REMOVED_NON_UTF_8
668 * @uses PMA_MYSQL_INT_VERSION
669 * @uses PMA_MYSQL_STR_VERSION
670 * @uses PMA_DBI_GETVAR_SESSION
671 * @uses PMA_DBI_fetch_value()
672 * @uses PMA_DBI_query()
673 * @uses PMA_DBI_get_variable()
674 * @uses $GLOBALS['collation_connection']
675 * @uses $GLOBALS['charset_connection']
676 * @uses $GLOBALS['available_languages']
677 * @uses $GLOBALS['mysql_charset_map']
678 * @uses $GLOBALS['charset']
679 * @uses $GLOBALS['lang']
680 * @uses $GLOBALS['cfg']['Lang']
681 * @uses $GLOBALS['cfg']['ColumnTypes']
682 * @uses defined()
683 * @uses explode()
684 * @uses sprintf()
685 * @uses intval()
686 * @uses define()
687 * @uses defined()
688 * @uses substr()
689 * @uses count()
690 * @param mixed $link mysql link resource|object
691 * @param boolean $is_controluser
692 */
693 function PMA_DBI_postConnect($link, $is_controluser = false)
694 {
695 if (!defined('PMA_MYSQL_INT_VERSION')) {
696 $mysql_version = PMA_DBI_fetch_value(
697 'SELECT VERSION()', 0, 0, $link, PMA_DBI_QUERY_STORE);
698 if ( $mysql_version ) {
699 $match = explode('.', $mysql_version);
700 define('PMA_MYSQL_INT_VERSION',
701 (int) sprintf('%d%02d%02d', $match[0], $match[1],
702 intval($match[2])));
703 define('PMA_MYSQL_STR_VERSION', $mysql_version);
704 unset($mysql_version, $match);
705 } else {
706 define('PMA_MYSQL_INT_VERSION', 32332);
707 define('PMA_MYSQL_STR_VERSION', '3.23.32');
708 }
709 }
710  
711 if (!defined('PMA_ENGINE_KEYWORD')) {
712 if (PMA_MYSQL_INT_VERSION >= 40102) {
713 define('PMA_ENGINE_KEYWORD','ENGINE');
714 } else {
715 define('PMA_ENGINE_KEYWORD','TYPE');
716 }
717 }
718  
719 if (PMA_MYSQL_INT_VERSION >= 40100) {
720  
721 // If $lang is defined and we are on MySQL >= 4.1.x,
722 // we auto-switch the lang to its UTF-8 version (if it exists and user
723 // didn't force language)
724 if ( !empty($GLOBALS['lang'])
725 && (substr($GLOBALS['lang'], -5) != 'utf-8')
726 && !isset($GLOBALS['cfg']['Lang']) ) {
727 $lang_utf_8_version =
728 substr($GLOBALS['lang'], 0, strpos($GLOBALS['lang'], '-'))
729 . '-utf-8';
730 if (!empty($GLOBALS['available_languages'][$lang_utf_8_version])) {
731 $GLOBALS['lang'] = $lang_utf_8_version;
732 $GLOBALS['charset'] = 'utf-8';
733 }
734 }
735  
736 // and we remove the non-UTF-8 choices to avoid confusion
737 if (!defined('PMA_REMOVED_NON_UTF_8')) {
738 foreach ( $GLOBALS['available_languages'] as $each_lang => $dummy ) {
739 if ( substr($each_lang, -5) != 'utf-8' ) {
740 unset( $GLOBALS['available_languages'][$each_lang] );
741 }
742 }
743 define('PMA_REMOVED_NON_UTF_8', 1);
744 }
745  
746 $mysql_charset = $GLOBALS['mysql_charset_map'][$GLOBALS['charset']];
747 if ( $is_controluser
748 || empty($GLOBALS['collation_connection'])
749 || (strpos($GLOBALS['collation_connection'], '_')
750 ? substr($GLOBALS['collation_connection'], 0, strpos($GLOBALS['collation_connection'], '_'))
751 : $GLOBALS['collation_connection']) == $mysql_charset) {
752  
753 PMA_DBI_query('SET NAMES ' . $mysql_charset . ';', $link,
754 PMA_DBI_QUERY_STORE);
755 } else {
756 PMA_DBI_query('SET CHARACTER SET ' . $mysql_charset . ';', $link,
757 PMA_DBI_QUERY_STORE);
758 }
759 if (!empty($GLOBALS['collation_connection'])) {
760 PMA_DBI_query('SET collation_connection = \'' . $GLOBALS['collation_connection'] . '\';',
761 $link, PMA_DBI_QUERY_STORE);
762 }
763 if (!$is_controluser) {
764 $GLOBALS['collation_connection'] = PMA_DBI_get_variable('collation_connection',
765 PMA_DBI_GETVAR_SESSION, $link);
766 $GLOBALS['charset_connection'] = PMA_DBI_get_variable('character_set_connection',
767 PMA_DBI_GETVAR_SESSION, $link);
768 }
769  
770 // Add some field types to the list, this needs to be done once per session!
771 if ($GLOBALS['cfg']['ColumnTypes'][count($GLOBALS['cfg']['ColumnTypes']) - 1] != 'VARBINARY') {
772 $GLOBALS['cfg']['ColumnTypes'][] = 'BINARY';
773 $GLOBALS['cfg']['ColumnTypes'][] = 'VARBINARY';
774 }
775  
776 } else {
777 require_once('./libraries/charset_conversion.lib.php');
778 }
779 }
780  
781 /**
782 * returns a single value from the given result or query,
783 * if the query or the result has more than one row or field
784 * the first field of the first row is returned
785 *
786 * <code>
787 * $sql = 'SELECT `name` FROM `user` WHERE `id` = 123';
788 * $user_name = PMA_DBI_fetch_value( $sql );
789 * // produces
790 * // $user_name = 'John Doe'
791 * </code>
792 *
793 * @uses is_string()
794 * @uses is_int()
795 * @uses PMA_DBI_try_query()
796 * @uses PMA_DBI_num_rows()
797 * @uses PMA_DBI_fetch_row()
798 * @uses PMA_DBI_fetch_assoc()
799 * @uses PMA_DBI_free_result()
800 * @param string|mysql_result $result query or mysql result
801 * @param integer $row_number row to fetch the value from,
802 * starting at 0, with 0 beeing default
803 * @param integer|string $field field to fetch the value from,
804 * starting at 0, with 0 beeing default
805 * @param resource $link mysql link
806 * @param mixed $options
807 * @return mixed value of first field in first row from result
808 * or false if not found
809 */
810 function PMA_DBI_fetch_value( $result, $row_number = 0, $field = 0, $link = null, $options = 0 ) {
811 $value = false;
812  
813 if ( is_string( $result ) ) {
814 $result = PMA_DBI_try_query( $result, $link, $options | PMA_DBI_QUERY_STORE );
815 }
816  
817 // return false if result is empty or false
818 // or requested row is larger than rows in result
819 if ( PMA_DBI_num_rows( $result ) < ( $row_number + 1 ) ) {
820 return $value;
821 }
822  
823 // if $field is an integer use non associative mysql fetch function
824 if ( is_int( $field ) ) {
825 $fetch_function = 'PMA_DBI_fetch_row';
826 } else {
827 $fetch_function = 'PMA_DBI_fetch_assoc';
828 }
829  
830 // get requested row
831 for ( $i = 0; $i <= $row_number; $i++ ) {
832 $row = $fetch_function( $result );
833 }
834 PMA_DBI_free_result( $result );
835  
836 // return requested field
837 if ( isset( $row[$field] ) ) {
838 $value = $row[$field];
839 }
840 unset( $row );
841  
842 return $value;
843 }
844  
845 /**
846 * returns only the first row from the result
847 *
848 * <code>
849 * $sql = 'SELECT * FROM `user` WHERE `id` = 123';
850 * $user = PMA_DBI_fetch_single_row( $sql );
851 * // produces
852 * // $user = array( 'id' => 123, 'name' => 'John Doe' )
853 * </code>
854 *
855 * @uses is_string()
856 * @uses PMA_DBI_try_query()
857 * @uses PMA_DBI_num_rows()
858 * @uses PMA_DBI_fetch_row()
859 * @uses PMA_DBI_fetch_assoc()
860 * @uses PMA_DBI_fetch_array()
861 * @uses PMA_DBI_free_result()
862 * @param string|mysql_result $result query or mysql result
863 * @param string $type NUM|ASSOC|BOTH
864 * returned array should either numeric
865 * associativ or booth
866 * @param resource $link mysql link
867 * @param mixed $options
868 * @return array|boolean first row from result
869 * or false if result is empty
870 */
871 function PMA_DBI_fetch_single_row( $result, $type = 'ASSOC', $link = null, $options = 0 ) {
872 if ( is_string( $result ) ) {
873 $result = PMA_DBI_try_query( $result, $link, $options | PMA_DBI_QUERY_STORE );
874 }
875  
876 // return null if result is empty or false
877 if ( ! PMA_DBI_num_rows( $result ) ) {
878 return false;
879 }
880  
881 switch ( $type ) {
882 case 'NUM' :
883 $fetch_function = 'PMA_DBI_fetch_row';
884 break;
885 case 'ASSOC' :
886 $fetch_function = 'PMA_DBI_fetch_assoc';
887 break;
888 case 'BOTH' :
889 default :
890 $fetch_function = 'PMA_DBI_fetch_array';
891 break;
892 }
893  
894 $row = $fetch_function( $result );
895 PMA_DBI_free_result( $result );
896 return $row;
897 }
898  
899 /**
900 * returns all rows in the resultset in one array
901 *
902 * <code>
903 * $sql = 'SELECT * FROM `user`';
904 * $users = PMA_DBI_fetch_result( $sql );
905 * // produces
906 * // $users[] = array( 'id' => 123, 'name' => 'John Doe' )
907 *
908 * $sql = 'SELECT `id`, `name` FROM `user`';
909 * $users = PMA_DBI_fetch_result( $sql, 'id' );
910 * // produces
911 * // $users['123'] = array( 'id' => 123, 'name' => 'John Doe' )
912 *
913 * $sql = 'SELECT `id`, `name` FROM `user`';
914 * $users = PMA_DBI_fetch_result( $sql, 0 );
915 * // produces
916 * // $users['123'] = array( 0 => 123, 1 => 'John Doe' )
917 *
918 * $sql = 'SELECT `id`, `name` FROM `user`';
919 * $users = PMA_DBI_fetch_result( $sql, 'id', 'name' );
920 * // or
921 * $users = PMA_DBI_fetch_result( $sql, 0, 1 );
922 * // produces
923 * // $users['123'] = 'John Doe'
924 *
925 * $sql = 'SELECT `name` FROM `user`';
926 * $users = PMA_DBI_fetch_result( $sql );
927 * // produces
928 * // $users[] = 'John Doe'
929 * </code>
930 *
931 * @uses is_string()
932 * @uses is_int()
933 * @uses PMA_DBI_try_query()
934 * @uses PMA_DBI_num_rows()
935 * @uses PMA_DBI_num_fields()
936 * @uses PMA_DBI_fetch_row()
937 * @uses PMA_DBI_fetch_assoc()
938 * @uses PMA_DBI_free_result()
939 * @param string|mysql_result $result query or mysql result
940 * @param string|integer $key field-name or offset
941 * used as key for array
942 * @param string|integer $value value-name or offset
943 * used as value for array
944 * @param resource $link mysql link
945 * @param mixed $options
946 * @return array resultrows or values indexed by $key
947 */
948 function PMA_DBI_fetch_result( $result, $key = null, $value = null,
949 $link = null, $options = 0 )
950 {
951 $resultrows = array();
952  
953 if ( is_string($result) ) {
954 $result = PMA_DBI_try_query($result, $link, $options);
955 }
956  
957 // return empty array if result is empty or false
958 if ( ! $result ) {
959 return $resultrows;
960 }
961  
962 $fetch_function = 'PMA_DBI_fetch_assoc';
963  
964 // no nested array if only one field is in result
965 if ( null === $key && 1 === PMA_DBI_num_fields($result) ) {
966 $value = 0;
967 $fetch_function = 'PMA_DBI_fetch_row';
968 }
969  
970 // if $key is an integer use non associative mysql fetch function
971 if ( is_int($key) ) {
972 $fetch_function = 'PMA_DBI_fetch_row';
973 }
974  
975 if ( null === $key && null === $value ) {
976 while ( $row = $fetch_function($result) ) {
977 $resultrows[] = $row;
978 }
979 } elseif ( null === $key ) {
980 while ( $row = $fetch_function($result) ) {
981 $resultrows[] = $row[$value];
982 }
983 } elseif ( null === $value ) {
984 if ( is_array($key) ) {
985 while ( $row = $fetch_function($result) ) {
986 $result_target =& $resultrows;
987 foreach ( $key as $key_index ) {
988 if ( ! isset( $result_target[$row[$key_index]] ) ) {
989 $result_target[$row[$key_index]] = array();
990 }
991 $result_target =& $result_target[$row[$key_index]];
992 }
993 $result_target = $row;
994 }
995 } else {
996 while ( $row = $fetch_function($result) ) {
997 $resultrows[$row[$key]] = $row;
998 }
999 }
1000 } else {
1001 if ( is_array($key) ) {
1002 while ( $row = $fetch_function($result) ) {
1003 $result_target =& $resultrows;
1004 foreach ( $key as $key_index ) {
1005 if ( ! isset( $result_target[$row[$key_index]] ) ) {
1006 $result_target[$row[$key_index]] = array();
1007 }
1008 $result_target =& $result_target[$row[$key_index]];
1009 }
1010 $result_target = $row[$value];
1011 }
1012 } else {
1013 while ( $row = $fetch_function($result) ) {
1014 $resultrows[$row[$key]] = $row[$value];
1015 }
1016 }
1017 }
1018  
1019 PMA_DBI_free_result($result);
1020 return $resultrows;
1021 }
1022  
1023 /**
1024 * return default table engine for given database
1025 *
1026 * @return string default table engine
1027 */
1028 function PMA_DBI_get_default_engine()
1029 {
1030 if ( PMA_MYSQL_INT_VERSION > 50002 ) {
1031 return PMA_DBI_fetch_value( 'SHOW VARIABLES LIKE \'storage_engine\';', 0, 1 );
1032 } else {
1033 return PMA_DBI_fetch_value( 'SHOW VARIABLES LIKE \'table_type\';', 0, 1 );
1034 }
1035 }
1036  
1037 ?>