libraries/tbl_select.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for the table-search page and zoom-search page
   5  *
   6  * Funtion PMA_tbl_getFields : Returns the fields of a table
   7  * Funtion PMA_tbl_search_getWhereClause : Returns the where clause for query generation
   8  *
   9  * @package phpMyAdmin
  10  */
  11
  12 require_once 'url_generating.lib.php';
  13
  14 /**
  15  * Sets the title for foreign keys display link.
  16  *
  17  * @param mixed  $propertiesIconic Type of icon property
  18  * @param string $pmaThemeImage    Icon Image
  19  *
  20  * @return string $str Value of the Title
  21  */
  22 function PMA_tbl_setTitle($propertiesIconic,$pmaThemeImage)
  23 {
  24     if ($propertiesIconic == true) {
  25         $str = '<img class="icon" width="16" height="16" src="' . $pmaThemeImage
  26             .'b_browse.png" alt="' . __('Browse foreign values') . '" title="'
  27             . __('Browse foreign values') . '" />';
  28
  29         if ($propertiesIconic === 'both') {
  30             $str .= __('Browse foreign values');
  31             return $str;
  32         }
  33     } else {
  34         return __('Browse foreign values');
  35     }
  36 }
  37
  38 /**
  39  * Gets all the fields of a table along with their types, collations
  40  * and whether null or not.
  41  *
  42  * @param string $table Selected table
  43  * @param string $db    Selected database
  44  *
  45  * @return array Array containing the field list, field types, collations
  46  * and null constraint
  47  */
  48 function PMA_tbl_getFields($table,$db)
  49 {
  50     // Gets the list and number of fields
  51     $result = PMA_DBI_query(
  52         'SHOW FULL FIELDS FROM ' . PMA_backquote($table) . ' FROM ' . PMA_backquote($db) . ';',
  53         null, PMA_DBI_QUERY_STORE
  54     );
  55     $fields_cnt = PMA_DBI_num_rows($result);
  56     $fields_list = $fields_null = $fields_type = $fields_collation = array();
  57     $geom_column_present = false;
  58     $geom_types = PMA_getGISDatatypes();
  59
  60     while ($row = PMA_DBI_fetch_assoc($result)) {
  61         $fields_list[] = $row['Field'];
  62         $type          = $row['Type'];
  63
  64         // check whether table contains geometric columns
  65         if (in_array($type, $geom_types)) {
  66             $geom_column_present = true;
  67         }
  68
  69         // reformat mysql query output
  70         if (strncasecmp($type, 'set', 3) == 0
  71             || strncasecmp($type, 'enum', 4) == 0
  72         ) {
  73             $type = str_replace(',', ', ', $type);
  74         } else {
  75             // strip the "BINARY" attribute, except if we find "BINARY(" because
  76             // this would be a BINARY or VARBINARY field type
  77             if (!preg_match('@BINARY[\(]@i', $type)) {
  78                 $type = preg_replace('@BINARY@i', '', $type);
  79             }
  80             $type = preg_replace('@ZEROFILL@i', '', $type);
  81             $type = preg_replace('@UNSIGNED@i', '', $type);
  82
  83             $type = strtolower($type);
  84         }
  85         if (empty($type)) {
  86             $type = '&nbsp;';
  87         }
  88         $fields_null[] = $row['Null'];
  89         $fields_type[] = $type;
  90         $fields_collation[] = ! empty($row['Collation']) && $row['Collation'] != 'NULL'
  91             ? $row['Collation']
  92             : '';
  93     } // end while
  94     PMA_DBI_free_result($result);
  95     unset($result, $type);
  96
  97     return array($fields_list, $fields_type, $fields_collation, $fields_null, $geom_column_present);
  98
  99 }
 100
 101 /**
 102  * Sets the table header for displaying a table in query-by-example format.
 103  *
 104  * @param bool $geom_column_present whether a geometry column is present
 105  *
 106  * @return HTML content, the tags and content for table header
 107  */
 108 function PMA_tbl_setTableHeader($geom_column_present = false)
 109 {
 110     // Display the Function column only if there is alteast one geomety colum
 111     $func = '';
 112     if ($geom_column_present) {
 113         $func = '<th>' . __('Function') . '</th>';
 114     }
 115
 116     return '<thead>
 117         <tr>' . $func . '<th>' .  __('Column') . '</th>
 118         <th>' .  __('Type') . '</th>
 119         <th>' .  __('Collation') . '</th>
 120         <th>' .  __('Operator') . '</th>
 121         <th>' .  __('Value') . '</th>
 122         </tr>
 123         </thead>';
 124 }
 125
 126 /**
 127  * Returns an array with necessary configrations to create
 128  * sub-tabs(Table Search and Zoom Search) in the table_select page.
 129  *
 130  * @return array Array containing configuration (icon, text, link, id, args)
 131  * of sub-tabs for Table Search and Zoom search
 132  */
 133 function PMA_tbl_getSubTabs()
 134 {
 135     $subtabs = array();
 136     $subtabs['search']['icon'] = 'b_search.png';
 137     $subtabs['search']['text'] = __('Table Search');
 138     $subtabs['search']['link'] = 'tbl_select.php';
 139     $subtabs['search']['id'] = 'tbl_search_id';
 140     $subtabs['search']['args']['pos'] = 0;
 141
 142     $subtabs['zoom']['icon'] = 'b_props.png';
 143     $subtabs['zoom']['link'] = 'tbl_zoom_select.php';
 144     $subtabs['zoom']['text'] = __('Zoom Search');
 145     $subtabs['zoom']['id'] = 'zoom_search_id';
 146
 147     return $subtabs;
 148 }
 149
 150 /**
 151  * Creates the HTML content for:
 152  * 1) Browsing foreign data for a field.
 153  * 2) Creating elements for search criteria input on fields.
 154  *
 155  * @param array  $foreigners      Array of foreign keys
 156  * @param array  $foreignData     Foreign keys data
 157  * @param string $field           Column name
 158  * @param string $tbl_fields_type Column type
 159  * @param int    $i               Column index
 160  * @param string $db              Selected database
 161  * @param string $table           Selected table
 162  * @param array  $titles          Selected title
 163  * @param int    $foreignMaxLimit Max limit of displaying foreign elements
 164  * @param array  $fields          Array of search criteria inputs
 165  * @param bool   $in_fbs          Whether we are in 'function based search'
 166  *
 167  * @return string HTML content for viewing foreing data and elements
 168  * for search criteria input.
 169  */
 170 function PMA_getForeignFields_Values($foreigners, $foreignData, $field, $tbl_fields_type, $i, $db, $table, $titles, $foreignMaxLimit, $fields, $in_fbs = false){
 171
 172     $str = '';
 173
 174     if ($foreigners && isset($foreigners[$field]) && is_array($foreignData['disp_row'])) {
 175         // f o r e i g n    k e y s
 176         $str .=  '            <select name="fields[' . $i . ']" id="fieldID_' . $i .'">' . "\n";
 177         // go back to first row
 178         // here, the 4th parameter is empty because there is no current
 179         // value of data for the dropdown (the search page initial values
 180         // are displayed empty)
 181         $str .= PMA_foreignDropdown($foreignData['disp_row'],
 182                 $foreignData['foreign_field'],
 183                 $foreignData['foreign_display'],
 184                 '', $foreignMaxLimit);
 185         $str .= '            </select>' . "\n";
 186     }
 187     elseif ($foreignData['foreign_link'] == true) {
 188         if(isset($fields[$i]) && is_string($fields[$i])){
 189          $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . '] " value="' . $fields[$i] . '"';
 190              'id="field_' . md5($field) . '[' . $i .']"
 191              class="textfield"/>' ;
 192         }
 193         else{
 194          $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . '] "';
 195              'id="field_' . md5($field) . '[' . $i .']"
 196              class="textfield" />' ;
 197         }
 198  ?>
 199     <?php $str .= '<script type="text/javascript">';
 200         // <![CDATA[
 201     $str .=  <<<EOT
 202 <a target="_blank" onclick="window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes'); return false" href="browse_foreigners.php?
 203 EOT;
 204         $str .= '' . PMA_generate_common_url($db, $table) .  '&amp;field=' . urlencode($field) . '&amp;fieldkey=' . $i . '">' . str_replace("'", "\'", $titles['Browse']) . '</a>';
 205         // ]]
 206         $str .= '</script>';
 207     } elseif (in_array($tbl_fields_type[$i], PMA_getGISDatatypes())) {
 208         // g e o m e t r y
 209         $str .= '<input type="text" name="fields[' . $i . ']"'
 210         .' size="40" class="textfield" id="field_' . $i . '" />' .  "\n";
 211
 212         if ($in_fbs) {
 213             $edit_url = 'gis_data_editor.php?' . PMA_generate_common_url();
 214             $edit_str = PMA_getIcon('b_edit.png', __('Edit/Insert'), true);
 215             $str .= '<span class="open_search_gis_editor">';
 216             $str .= PMA_linkOrButton($edit_url, $edit_str, array(), false, false, '_blank');
 217             $str .= '</span>';
 218         }
 219     } elseif (strncasecmp($tbl_fields_type[$i], 'enum', 4) == 0) {
 220         // e n u m s
 221         $enum_value=explode(', ', str_replace("'", '', substr($tbl_fields_type[$i], 5, -1)));
 222         $cnt_enum_value = count($enum_value);
 223         $str .= '<select name="fields[' . ($i) . '][]" id="fieldID_' . $i .'"'
 224                  .' multiple="multiple" size="' . min(3, $cnt_enum_value) . '">' . "\n";
 225                 for ($j = 0; $j < $cnt_enum_value; $j++) {
 226                     if(isset($fields[$i]) && is_array($fields[$i]) && in_array($enum_value[$j],$fields[$i])){
 227                         $str .= '                <option value="' . $enum_value[$j] . '" Selected>'
 228                                         . $enum_value[$j] . '</option>';
 229                         }
 230                         else{
 231                                 $str .= '                <option value="' . $enum_value[$j] . '">'
 232                                         . $enum_value[$j] . '</option>';
 233                         }
 234                 } // end for
 235         $str .= '            </select>' . "\n";
 236     }
 237     else {
 238         // o t h e r   c a s e s
 239         $the_class = 'textfield';
 240         $type = $tbl_fields_type[$i];
 241         if ($type == 'date') {
 242             $the_class .= ' datefield';
 243         } elseif ($type == 'datetime' || substr($type, 0, 9) == 'timestamp') {
 244             $the_class .= ' datetimefield';
 245         }
 246         if(isset($fields[$i]) && is_string($fields[$i])){
 247             $str .= '            <input type="text" name="fields[' . $i . ']" '
 248                     .' size="40" class="' . $the_class . '" id="fieldID_' . $i .'" value = "' . $fields[$i] . '"/>' .  "\n";
 249         }
 250         else{
 251             $str .= '            <input type="text" name="fields[' . $i . ']"'
 252                     .' size="40" class="' . $the_class . '" id="fieldID_' . $i .'" />' .  "\n";
 253         }
 254    };
 255    return $str;
 256
 257 }
 258
 259
 260 /**
 261  * Return the where clause for query generation based on the inputs provided.
 262  *
 263  * @param mixed  $fields     Search criteria input
 264  * @param string $names      Name of the column on which search is submitted
 265  * @param string $types      Type of the field
 266  * @param string $collations Field collation
 267  * @param string $func_type  Search fucntion/operator
 268  * @param bool   $unaryFlag  Whether operator unary or not
 269  * @param bool   $geom_func  Whether geometry functions should be applied
 270  *
 271  * @return string HTML content for viewing foreing data and elements
 272  * for search criteria input.
 273  */
 274 function PMA_tbl_search_getWhereClause($fields, $names, $types, $collations, $func_type, $unaryFlag, $geom_func = null){
 275
 276     /**
 277      * @todo move this to a more apropriate place
 278      */
 279     $geom_unary_functions = array(
 280         'IsEmpty' => 1,
 281         'IsSimple' => 1,
 282         'IsRing' => 1,
 283         'IsClosed' => 1,
 284     );
 285
 286     $w = '';
 287     // If geometry function is set apply it to the field name
 288     if ($geom_func != null && trim($geom_func) != '') {
 289         // Get details about the geometry fucntions
 290         $geom_funcs = PMA_getGISFunctions($types, true, false);
 291
 292         // If the function takes a single parameter
 293         if ($geom_funcs[$geom_func]['params'] == 1) {
 294             $backquoted_name = $geom_func . '(' . PMA_backquote($names) . ')';
 295         } else {
 296             // If the function takes two parameters
 297             // create gis data from the string
 298             $gis_data = PMA_createGISData($fields);
 299
 300             $w = $geom_func . '(' . PMA_backquote($names) . ',' . $gis_data . ')';
 301             return $w;
 302         }
 303
 304         // New output type is the output type of the function being applied
 305         $types = $geom_funcs[$geom_func]['type'];
 306
 307         // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
 308         if (isset($geom_unary_functions[$geom_func]) && trim($fields) == '') {
 309             $w = $backquoted_name;
 310             return $w;
 311         }
 312     } else {
 313         $backquoted_name = PMA_backquote($names);
 314     }
 315
 316     if ($unaryFlag) {
 317         $fields = '';
 318         $w = $backquoted_name . ' ' . $func_type;
 319
 320     } elseif (in_array($types, PMA_getGISDatatypes()) && ! empty($fields)) {
 321         // create gis data from the string
 322         $gis_data = PMA_createGISData($fields);
 323         $w = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
 324
 325     } elseif (strncasecmp($types, 'enum', 4) == 0) {
 326         if (!empty($fields)) {
 327             if (! is_array($fields)) {
 328                 $fields = explode(',', $fields);
 329             }
 330             $enum_selected_count = count($fields);
 331             if ($func_type == '=' && $enum_selected_count > 1) {
 332                 $func_type    = 'IN';
 333                 $parens_open  = '(';
 334                 $parens_close = ')';
 335
 336             } elseif ($func_type == '!=' && $enum_selected_count > 1) {
 337                 $func_type    = 'NOT IN';
 338                 $parens_open  = '(';
 339                 $parens_close = ')';
 340
 341             } else {
 342                 $parens_open  = '';
 343                 $parens_close = '';
 344             }
 345             $enum_where = '\'' . PMA_sqlAddslashes($fields[0]) . '\'';
 346             for ($e = 1; $e < $enum_selected_count; $e++) {
 347                 $enum_where .= ', \'' . PMA_sqlAddslashes($fields[$e]) . '\'';
 348             }
 349
 350             $w = $backquoted_name . ' ' . $func_type . ' ' . $parens_open . $enum_where . $parens_close;
 351         }
 352
 353     } elseif ($fields != '') {
 354         // For these types we quote the value. Even if it's another type (like INT),
 355         // for a LIKE we always quote the value. MySQL converts strings to numbers
 356         // and numbers to strings as necessary during the comparison
 357         if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
 358             || strpos(' ' . $func_type, 'LIKE')
 359         ) {
 360             $quot = '\'';
 361         } else {
 362             $quot = '';
 363         }
 364
 365         // LIKE %...%
 366         if ($func_type == 'LIKE %...%') {
 367             $func_type = 'LIKE';
 368             $fields = '%' . $fields . '%';
 369         }
 370         if ($func_type == 'REGEXP ^...$') {
 371             $func_type = 'REGEXP';
 372             $fields = '^' . $fields . '$';
 373         }
 374
 375         if ($func_type == 'IN (...)'
 376             || $func_type == 'NOT IN (...)'
 377             || $func_type == 'BETWEEN'
 378             || $func_type == 'NOT BETWEEN'
 379         ) {
 380             $func_type = str_replace(' (...)', '', $func_type);
 381
 382             // quote values one by one
 383             $values = explode(',', $fields);
 384             foreach ($values as &$value) {
 385                 $value = $quot . PMA_sqlAddslashes(trim($value)) . $quot;
 386             }
 387
 388             if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
 389                 $w = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ? $values[0] : '')
 390                     . ' AND ' . (isset($values[1]) ? $values[1] : '');
 391             } else {
 392                 $w = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')';
 393             }
 394         } else {
 395             $w = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_sqlAddslashes($fields) . $quot;;
 396         }
 397     } // end if
 398
 399     return $w;
 400 }
 401
 402 /**
 403  * Formats a SVG plot for the query results.
 404  *
 405  * @param array $data      Data for the status chart
 406  * @param array &$settings Settings used to generate the chart
 407  *
 408  * @return string HTML and JS code for the SVG plot
 409  */
 410 function PMA_SVG_scatter_plot($data, &$settings)
 411 {
 412     include_once './libraries/svg_plot/pma_scatter_plot.php';
 413
 414     if (empty($data)) {
 415         // empty data
 416         return '';
 417     } else {
 418         $scatter_plot = new PMA_Scatter_Plot($data, $settings);
 419
 420         if ($settings != null) {
 421             foreach ($scatter_plot->getSettings() as $setting => $val) {
 422                 if (! isset($settings[$setting])) {
 423                     $settings[$setting] = $val;
 424                 }
 425             }
 426         }
 427         return $scatter_plot->asSVG();
 428     }
 429 }
 430 ?>