sqlparser.lib.php

来自「php绿色服务器,让大家试用greenamp」· PHP 代码 · 共 1,652 行 · 第 1/5 页

                   $seen_group = FALSE;
                   $in_having = FALSE;
                   $in_group_by = FALSE;
                   $in_where = FALSE;
                   $in_select_expr = FALSE;
                   $in_from = FALSE;
               }
               if ($upper_data == 'HAVING') {
                   $in_having = TRUE;
                   $having_clause = '';
                   $seen_group = FALSE;
                   $seen_order = FALSE;
                   $in_group_by = FALSE;
                   $in_order_by = FALSE;
                   $in_where = FALSE;
                   $in_select_expr = FALSE;
                   $in_from = FALSE;
               }

               if ($upper_data == 'WHERE') {
                   $in_where = TRUE;
                   $where_clause = '';
                   $where_clause_identifiers = array();
                   $seen_group = FALSE;
                   $seen_order = FALSE;
                   $in_group_by = FALSE;
                   $in_order_by = FALSE;
                   $in_having = FALSE;
                   $in_select_expr = FALSE;
                   $in_from = FALSE;
               }

               if ($upper_data == 'BY') {
                   if ($seen_group) {
                       $in_group_by = TRUE;
                       $group_by_clause = '';
                   }
                   if ($seen_order) {
                       $in_order_by = TRUE;
                       $order_by_clause = '';
                   }
               }

               // if we find one of the words that could end the clause
               if (PMA_STR_binarySearchInArr($upper_data, $words_ending_clauses, $words_ending_clauses_cnt)) {

                   $in_group_by = FALSE;
                   $in_order_by = FALSE;
                   $in_having   = FALSE;
                   $in_where    = FALSE;
                   $in_select_expr = FALSE;
                   $in_from = FALSE;
               }

           } // endif (reservedWord)


           // do not add a blank after a function name
           // TODO: can we combine loop 2 and loop 1? 
           // some code is repeated here...

           $sep=' ';
           if ($arr[$i]['type'] == 'alpha_functionName') {
               $sep='';
               $upper_data = strtoupper($arr[$i]['data']);
               if ($upper_data =='GROUP_CONCAT') {
                   $in_group_concat = TRUE;
                   $number_of_brackets_in_group_concat = 0;
               }
           }

           if ($arr[$i]['type'] == 'punct_bracket_open_round') {
               if ($in_group_concat) {
                  $number_of_brackets_in_group_concat++;
               }
           }
           if ($arr[$i]['type'] == 'punct_bracket_close_round') {
               if ($in_group_concat) {
                  $number_of_brackets_in_group_concat--;
                  if ($number_of_brackets_in_group_concat == 0) {
                     $in_group_concat = FALSE;
                  }
               }
           }

           if ($in_select_expr && $upper_data != 'SELECT' && $upper_data != 'DISTINCT') {
               $select_expr_clause .= $arr[$i]['data'] . $sep;
           }
           if ($in_from && $upper_data != 'FROM') {
               $from_clause .= $arr[$i]['data'] . $sep;
           }
           if ($in_group_by && $upper_data != 'GROUP' && $upper_data != 'BY') {
               $group_by_clause .= $arr[$i]['data'] . $sep;
           }
           if ($in_order_by && $upper_data != 'ORDER' && $upper_data != 'BY') {
               $order_by_clause .= $arr[$i]['data'] . $sep;
           }
           if ($in_having && $upper_data != 'HAVING') {
               $having_clause .= $arr[$i]['data'] . $sep;
           }
           if ($in_where && $upper_data != 'WHERE') {
               $where_clause .= $arr[$i]['data'] . $sep;

               if (($arr[$i]['type'] == 'quote_backtick')
                || ($arr[$i]['type'] == 'alpha_identifier')) {
                   $where_clause_identifiers[] = $arr[$i]['data'];
               }
           }

           // clear $upper_data for next iteration
           $upper_data='';

        } // end for $i (loop #2)

        // -----------------------------------------------------
        // loop #3: foreign keys
        // (for now, check only the first query)
        // (for now, identifiers must be backquoted)

        $seen_foreign = FALSE;
        $seen_references = FALSE;
        $seen_constraint = FALSE;
        $in_bracket = FALSE;
        $foreign_key_number = -1;

        for ($i = 0; $i < $size; $i++) {
        // DEBUG echo "<b>" . $arr[$i]['data'] . "</b> " . $arr[$i]['type'] . "<br />";
            if ($arr[$i]['type'] == 'alpha_reservedWord') {
               $upper_data = strtoupper($arr[$i]['data']);

               if ($upper_data == 'CONSTRAINT') {
                   $foreign_key_number++;
                   $seen_foreign = FALSE;
                   $seen_references = FALSE;
                   $seen_constraint = TRUE;
               }
               if ($upper_data == 'FOREIGN') {
                   $seen_foreign = TRUE;
                   $seen_references = FALSE;
                   $seen_constraint = FALSE;
               }
               if ($upper_data == 'REFERENCES') {
                   $seen_foreign = FALSE;
                   $seen_references = TRUE;
                   $seen_constraint = FALSE;
               }


              // [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
              // [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]

              // but we set ['on_delete'] or ['on_cascade'] to
              // CASCADE | SET_NULL | NO_ACTION | RESTRICT

               if ($upper_data == 'ON') {
                   unset($clause);
                   if ($arr[$i+1]['type'] == 'alpha_reservedWord') {
                       $second_upper_data = strtoupper($arr[$i+1]['data']);
                       if ($second_upper_data == 'DELETE') {
                            $clause = 'on_delete';
                       }
                       if ($second_upper_data == 'UPDATE') {
                            $clause = 'on_update';
                       }
                       if (isset($clause)
                       && ($arr[$i+2]['type'] == 'alpha_reservedWord'

             // ugly workaround because currently, NO is not
             // in the list of reserved words in sqlparser.data
             // (we got a bug report about not being able to use
             // 'no' as an identifier)
                           || ($arr[$i+2]['type'] == 'alpha_identifier'
                              && strtoupper($arr[$i+2]['data'])=='NO') )
                          ) {
                          $third_upper_data = strtoupper($arr[$i+2]['data']);
                          if ($third_upper_data == 'CASCADE'
                           || $third_upper_data == 'RESTRICT') {
                              $value = $third_upper_data;
                          } elseif ($third_upper_data == 'SET'
                                 || $third_upper_data == 'NO') {
                              if ($arr[$i+3]['type'] == 'alpha_reservedWord') {
                                  $value = $third_upper_data . '_' . strtoupper($arr[$i+3]['data']);
                              }
                          } else {
                          // for example: ON UPDATE CURRENT_TIMESTAMP
                          // which is not for a foreign key
                              $value = '';
                          }
                          if (!empty($value)) {
                              $foreign[$foreign_key_number][$clause] = $value;
                          }
                       }
                   }
               }

            }

            if ($arr[$i]['type'] == 'punct_bracket_open_round') {
                $in_bracket = TRUE;
            }

            if ($arr[$i]['type'] == 'punct_bracket_close_round') {
                $in_bracket = FALSE;
                if ($seen_references) {
                    $seen_references = FALSE;
                }
            }

            if (($arr[$i]['type'] == 'quote_backtick')) {

                if ($seen_constraint) {
                    // remove backquotes
                    $identifier = str_replace('`','',$arr[$i]['data']);
                    $foreign[$foreign_key_number]['constraint'] = $identifier;
                }
                if ($seen_foreign && $in_bracket) {
                    // remove backquotes
                    $identifier = str_replace('`','',$arr[$i]['data']);
                    $foreign[$foreign_key_number]['index_list'][] = $identifier;
                }

                if ($seen_references) {
                    $identifier = str_replace('`','',$arr[$i]['data']);
                    if ($in_bracket) {
                        $foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
                    } else {
                        // for MySQL 4.0.18, identifier is
                        // `table` or `db`.`table` 
                        // first pass will pick the db name
                        // next pass will execute the else and pick the
                        // db name in $db_table[0]
                        if ($arr[$i+1]['type'] == 'punct_qualifier') {
                                $foreign[$foreign_key_number]['ref_db_name'] = $identifier;
                        } else {
                        // for MySQL 4.0.16, identifier is 
                        // `table` or `db.table`
                            $db_table = explode('.',$identifier);
                            if (isset($db_table[1])) {
                                $foreign[$foreign_key_number]['ref_db_name'] = $db_table[0];
                                $foreign[$foreign_key_number]['ref_table_name'] = $db_table[1];
                            } else {
                                $foreign[$foreign_key_number]['ref_table_name'] = $db_table[0];
                            }
                        }    
                    }
                }
            }
        } // end for $i (loop #3)

        if (isset($foreign)) {
            $subresult['foreign_keys'] = $foreign;
        }
        //DEBUG print_r($foreign);

        if (isset($select_expr_clause)) {
            $subresult['select_expr_clause'] = $select_expr_clause;
        }
        if (isset($from_clause)) {
            $subresult['from_clause'] = $from_clause;
        }
        if (isset($group_by_clause)) {
            $subresult['group_by_clause'] = $group_by_clause;
        }
        if (isset($order_by_clause)) {
            $subresult['order_by_clause'] = $order_by_clause;
        }
        if (isset($having_clause)) {
            $subresult['having_clause'] = $having_clause;
        }
        if (isset($where_clause)) {
            $subresult['where_clause'] = $where_clause;
        }
        if (isset($where_clause_identifiers)) {
            $subresult['where_clause_identifiers'] = $where_clause_identifiers;
        }

        if (isset($position_of_first_select)) {
            $subresult['position_of_first_select'] = $position_of_first_select;
        }

        // They are naughty and didn't have a trailing semi-colon,
        // then still handle it properly
        if ($subresult['querytype'] != '') {
            $result[] = $subresult;
        }
        return $result;
    } // end of the "PMA_SQP_analyze()" function


    /**
     * Colorizes SQL queries html formatted
     *
     * @param  array   The SQL queries html formatted
     *
     * @return array   The colorized SQL queries
     *
     * @access public
     */
    function PMA_SQP_formatHtml_colorize($arr)
    {
        $i         = $GLOBALS['PMA_strpos']($arr['type'], '_');
        $class     = '';
        if ($i > 0) {
            $class = 'syntax_' . PMA_substr($arr['type'], 0, $i) . ' ';
        }

        $class     .= 'syntax_' . $arr['type'];

        //TODO: check why adding a "\n" after the </span> would cause extra
        //      blanks to be displayed:
        //      SELECT p . person_name

        return '<span class="' . $class . '">' . htmlspecialchars($arr['data']) . '</span>';
    } // end of the "PMA_SQP_formatHtml_colorize()" function


    /**
     * Formats SQL queries to html
     *
     * @param  array   The SQL queries
     * @param  string  mode
     * @param  integer starting token
     * @param  integer number of tokens to format, -1 = all
     *
     * @return string  The formatted SQL queries
     *
     * @access public