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