PHP PHPSQLParser类代码示例

 public static function query($query, $params = array())
 {
     $default_comparator = "binary";
     if (preg_match("/^\\s*SHOW TABLES\$/i", $query)) {
         return MMB::showTables();
     }
     $parser = new \PHPSQLParser();
     $parsed = $parser->parse($query);
     $parsed["SQL"] = $query;
     self::debug(print_r($parsed, true));
     if (isset($parsed["SELECT"])) {
         return self::processSelectStatement($parsed);
     } else {
         if (isset($parsed["INSERT"], $parsed["VALUES"])) {
             return self::processInsertStatement($parsed);
         } else {
             if (isset($parsed["UPDATE"])) {
                 print_r($parsed);
                 return self::processUpdateStatement($parsed);
             } else {
                 if (isset($parsed["DELETE"])) {
                     return self::processDeleteStatement($parsed);
                 }
             }
         }
     }
     if ($params["link_identifier"]) {
         return \mysql_query($query, $params["link_identifier"]);
     }
     return array("1");
 }

 public function testQueryIsParsedWithoutErrors()
 {
     $sql = '(SELECT * FROM users) ORDER BY id';
     $parser = new PHPSQLParser();
     $result = $parser->parse($sql);
     $this->assertInternalType('array', $result);
 }

 /**
  * @param string $query
  */
 public function __construct($query)
 {
     $parser = new \PHPSQLParser();
     $this->parsedQuery = $parser->parse($query);
     $this->rawQuery = $query;
     $this->hash = sha1(serialize($this->parsedQuery));
 }

 public function testShouldHandleJoinableQueriesCorrectly()
 {
     $query = 'SELECT
         c.id,
         c.isin,
         c.currency,
         c.issuer,
         c.issuer_price,
         h.current_price,
         c.trading_market,
         bc.barrier_level,
         gc.participation_rate,
         c.type
     FROM
         history h
         LEFT JOIN certificate c ON (h.certificate_id = c.id)
         INNER JOIN history h2 ON (h.certificate_id = h2.certificate_id)
         LEFT JOIN bonus_certificate bc ON (c.id = bc.certificate_id)
         LEFT JOIN guarantee_certificate gc ON (c.id = gc.certificate_id)
     WHERE
         h.created = h2.created
         AND
         c.type = 2
     GROUP BY c.id';
     $parser = new \PHPSQLParser();
     $adapter = new \Nfx\SqlQueryBuilder\Adapter\ZendFrameworkAdapter();
     $adapter->setParsedQuery($parser->parse($query));
     $builder = $adapter->getBuilder();
     $expected = '...';
     $this->assertInstanceOf('Zend\\Db\\Sql\\Select', $builder);
     $this->assertEquals($expected, $builder->getSqlString());
 }

 public function testQueryHashing()
 {
     $sql = "SELECT foo FROM bar WHERE baz";
     $q = new \Pseudo\ParsedQuery($sql);
     $p = new \PHPSQLParser();
     $parsed = $p->parse($sql);
     $hashed = sha1(serialize($parsed));
     $this->assertEquals($hashed, $q->getHash());
 }

 public function testEmptyProcessSelectExpr()
 {
     $parser = new PHPSQLParser();
     //the key part of the sql string is the parenthesis.  This use case is more prone to fail in mssql environments
     //this will eventually result in PHPSQLParser::process_select_expr() processing an empty string, which is the
     //error we are testing for.
     $sql = '(SELECT  contacts.*  FROM contacts  ) ORDER  BY  contacts.phone_work asc';
     $sqlARR = $parser->parse($sql);
     // assert an array was returned.  If an error occurred a php fatal error will bubble up and nothing will be returned
     $this->assertInternalType('array', $sqlARR, "there was an error while parsing the sql string: " . $sql);
 }

 /**
  * Parse SQL query WHERE and ORDER BY clauses and validate that nothing bad is happening there
  * @param string $where
  * @param string $order_by
  * @return bool
  */
 public function validateQueryClauses($where, $order_by = '')
 {
     if (empty($where) && empty($order_by)) {
         return true;
     }
     if (empty($where) && !empty($order_by)) {
         $where = "deleted=0";
     }
     $parser = new PHPSQLParser();
     $testquery = "SELECT dummy FROM dummytable WHERE {$where}";
     $clauses = 3;
     if (!empty($order_by)) {
         $testquery .= " ORDER BY {$order_by}";
         $clauses++;
     }
     $parsed = $parser->parse($testquery);
     //$GLOBALS['log']->debug("PARSE: ".var_export($parsed, true));
     if (count($parsed) != $clauses) {
         // we assume: SELECT, FROM, WHERE, maybe ORDER
         return false;
     }
     $parts = array_keys($parsed);
     if ($parts[0] != "SELECT" || $parts[1] != "FROM" || $parts[2] != "WHERE") {
         // check the keys to be SELECT, FROM, WHERE
         return false;
     }
     if (!empty($order_by) && $parts[3] != "ORDER") {
         // extra key is ORDER
         return false;
     }
     // verify SELECT didn't change
     if (count($parsed["SELECT"]) != 1 || $parsed["SELECT"][0] !== array('expr_type' => 'colref', 'alias' => '`dummy`', 'base_expr' => 'dummy', 'sub_tree' => false)) {
         $GLOBALS['log']->debug("validation failed SELECT");
         return false;
     }
     // verify FROM didn't change
     if (count($parsed["FROM"]) != 1 || $parsed["FROM"][0] !== array('table' => 'dummytable', 'alias' => 'dummytable', 'join_type' => 'JOIN', 'ref_type' => '', 'ref_clause' => '', 'base_expr' => false, 'sub_tree' => false)) {
         $GLOBALS['log']->debug("validation failed FROM");
         return false;
     }
     // check WHERE
     if (!$this->validateExpression($parsed["WHERE"], true)) {
         $GLOBALS['log']->debug("validation failed WHERE");
         return false;
     }
     // check ORDER
     if (!empty($order_by) && !$this->validateExpression($parsed["ORDER"])) {
         $GLOBALS['log']->debug("validation failed ORDER");
         return false;
     }
     return true;
 }

<?php

require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
$parser = new PHPSQLParser();
$sql = 'SELECT
1';
$p = $parser->parse($sql);
ok(count($p) == 1 && count($p['SELECT']) == 1);
ok($p['SELECT'][0]['expr_type'] == 'const');
ok($p['SELECT'][0]['base_expr'] == '1');
ok($p['SELECT'][0]['sub_tree'] == '');
$sql = 'SELECT 1+2 c1, 1+2 as c2, 1+2,  sum(a) sum_a_alias,a,a an_alias, a as another_alias,terminate
          from some_table an_alias
	where d > 5;';
$parser->parse($sql);
$p = $parser->parsed;
ok(count($p) == 3 && count($p['SELECT']) == 8);
ok($p['SELECT'][count($p['SELECT']) - 1]['base_expr'] == 'terminate');
ok(count($p) == 3 && count($p['FROM']) == 1);
ok(count($p) == 3 && count($p['WHERE']) == 3);
$parser->parse('SELECT NOW( ),now(),sysdate( ),sysdate () as now');
ok($parser->parsed['SELECT'][3]['base_expr'] == 'sysdate');
$sql = " SELECT a.*, surveyls_title, surveyls_description, surveyls_welcometext, surveyls_url  FROM SURVEYS AS a INNER JOIN SURVEYS_LANGUAGESETTINGS on (surveyls_survey_id=a.sid and surveyls_language=a.language)  order by active DESC, surveyls_title";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'select1.serialized');
eq_array($p, $expected, 'a test for ref_clauses');
$sql = "SELECT pl_namespace,pl_title FROM `pagelinks` WHERE pl_from = '1' FOR UPDATE";
$parser->parse($sql);
$p = $parser->parsed;

<?php

require_once dirname(__FILE__) . '/../../../src/PHPSQLParser.php';
require_once dirname(__FILE__) . '/../../../src/PHPSQLCreator.php';
require_once dirname(__FILE__) . '/../../test-more.php';
$query = "UPDATE t1 SET c1 = -c2";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue127.sql', false);
ok($created === $expected, 'unary operator');

<?php

require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
$parser = new PHPSQLParser();
$sql = 'SELECT  SUM( 10 ) as test FROM account';
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized');
eq_array($p, $expected, 'only space characters within SQL statement');
$sql = "SELECT\tSUM( 10 ) \tas test FROM account";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized');
// should be the same as above
eq_array($p, $expected, 'tab character within SQL statement');

<?php

require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
// TODO: not solved, charsets are not possible at the moment
$parser = new PHPSQLParser();
$sql = "SELECT _utf8'hi'";
$parser->parse($sql, false);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue50.serialized');
eq_array($p, $expected, 'does not die if query contains _utf8');
$sql = "SELECT _utf8'hi' COLLATE latin1_german1_ci";
$parser->parse($sql, false);
$p = $parser->parsed;
# hex value
$sql = "SELECT _utf8 x'AABBCC'";
$sql = "SELECT _utf8 0xAABBCC";
# binary value
$sql = "SELECT _utf8 b'0001'";
$sql = "SELECT _utf8 0b0001";

<?php

require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
try {
    $sql = "SELECT PERCENTILE(xyz, 90) as percentile from some_table";
    $parser = new PHPSQLParser();
    $parser->addCustomFunction("percentile");
    $p = $parser->parse($sql, true);
} catch (Exception $e) {
    $p = array();
}
ok($p['SELECT'][0]['expr_type'] === ExpressionType::CUSTOM_FUNCTION, 'custom function within SELECT clause');

#!/usr/bin/env php
<?php 
/**
 * you cannot execute this script within Eclipse PHP
 * because of the limited output buffer. Try to run it
 * directly within a shell.
 */
require_once dirname(__FILE__) . '/../src/PHPSQLParser.php';
$parser = new PHPSQLParser();
// var_dump($parser);
// var_dump($argv);
// echo "Argsize =\n";
// echo sizeof($argv) . "\n";
if (sizeof($argv) < 2) {
    // echo "Not enough arguments...\n";
    // echo "Checking if url parameter";
    $sql = $_GET['kqlq'];
    echo json_encode($parser->parse($sql, true));
    // echo "First argument has to be KQL query\n";
    // exit(1);
} else {
    $sql = $argv[1];
    // echo json_encode($parser->parse($sql, true));
    print_r(json_encode($parser->parse($sql, true)));
}
/* You can use the ->parse() method too.  The parsed structure is returned, and 
   also available in the ->parsed property. */
// $sql = 'SELECT \'a,b,c\'
//           from \'some_table an_alias\'
// 	where \'d > 5;\'';
// $sql = $argv[1];

 function GetAggregates($_arr)
 {
     $_parser = new PHPSQLParser();
     $_parser->parse($this->SelectCommand);
     $_parsed_query = $_parser->parsed;
     //Filter
     $_filters = $this->Filters;
     if (count($_filters) > 0) {
         for ($i = 0; $i < sizeof($_filters); $i++) {
             if (!isset($_parsed_query["WHERE"])) {
                 $_parsed_query["WHERE"] = array();
             } else {
                 array_push($_parsed_query["WHERE"], array("expr_type" => "operator", "base_expr" => "and", "sub_tree" => false));
             }
             $_epx = $this->GetFilterExpression($_filters[$i]);
             array_push($_parsed_query["WHERE"], array("expr_type" => "colref", "base_expr" => $_epx["field"], "sub_tree" => false));
             array_push($_parsed_query["WHERE"], array("expr_type" => "operator", "base_expr" => $_epx["expression"], "sub_tree" => false));
             array_push($_parsed_query["WHERE"], array("expr_type" => "const", "base_expr" => $_epx["value"], "sub_tree" => false));
         }
     }
     //Sort
     $_sorts = $this->Sorts;
     if (count($_sorts) > 0) {
         for ($i = 0; $i < sizeof($_sorts); $i++) {
             if (!isset($_parsed_query["ORDER"])) {
                 $_parsed_query["ORDER"] = array();
             }
             array_push($_parsed_query["ORDER"], array("expr_type" => "colref", "base_expr" => $_sorts[$i]->Field, "sub_tree" => false, "direction" => $_sorts[$i]->Order));
         }
     }
     //Group
     $_groups = $this->Groups;
     if (count($_groups) > 0) {
         for ($i = 0; $i < sizeof($_groups); $i++) {
             if (!isset($_parsed_query["GROUP"])) {
                 $_parsed_query["GROUP"] = array();
             }
             array_push($_parsed_query["GROUP"], array("expr_type" => "colref", "base_expr" => $_groups[$i]->Field, "sub_tree" => false));
         }
     }
     //SELECT
     $_parsed_query["SELECT"] = array(array("expr_type" => "colref", "base_expr" => "{arggregate}", "sub_tree" => false, "alias" => false));
     $_text = "";
     foreach ($_arr as $_aggregate) {
         if (strpos("-|min|max|first|last|count|sum|avg|", "|" . strtolower($_aggregate["Aggregate"]) . "|") > 0) {
             $_text .= ", " . $_aggregate["Aggregate"] . "(" . $_aggregate["DataField"] . ") as " . $_aggregate["Key"];
         }
     }
     $_agg_result = array();
     if ($_text != "") {
         $_text = substr($_text, 2);
         $_creator = new PHPSQLCreator($_parsed_query);
         $_select_command = $_creator->created;
         $_select_command = str_replace("{arggregate}", $_text, $_select_command);
         $_result = mysql_query($_select_command, $this->_Link);
         $_agg_result = mysql_fetch_assoc($_result);
         mysql_free_result($_result);
         //-----
     }
     return $_agg_result;
 }

 /**
  * FIXME: verify and thoroughly test this code, these regexps look fishy
  * @see DBManager::limitQuery()
  */
 public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
 {
     $start = (int) $start;
     $count = (int) $count;
     $newSQL = $sql;
     $distinctSQLARRAY = array();
     if (strpos($sql, "UNION") && !preg_match("/(')(UNION).?(')/i", $sql)) {
         $newSQL = $this->handleUnionLimitQuery($sql, $start, $count);
     } else {
         if ($start < 0) {
             $start = 0;
         }
         $GLOBALS['log']->debug(print_r(func_get_args(), true));
         $this->lastsql = $sql;
         $matches = array();
         preg_match('/^(.*SELECT\\b)(.*?\\bFROM\\b.*\\bWHERE\\b)(.*)$/isU', $sql, $matches);
         if (!empty($matches[3])) {
             if ($start == 0) {
                 $match_two = strtolower($matches[2]);
                 if (!strpos($match_two, "distinct") > 0 && strpos($match_two, "distinct") !== 0) {
                     $orderByMatch = array();
                     preg_match('/^(.*)(\\bORDER BY\\b)(.*)$/is', $matches[3], $orderByMatch);
                     if (!empty($orderByMatch[3])) {
                         $selectPart = array();
                         preg_match('/^(.*)(\\bFROM\\b.*)$/isU', $matches[2], $selectPart);
                         $newSQL = "SELECT TOP {$count} * FROM\n                                (\n                                    " . $matches[1] . $selectPart[1] . ", ROW_NUMBER()\n                                    OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number\n                                    " . $selectPart[2] . $orderByMatch[1] . "\n                                ) AS a\n                                WHERE row_number > {$start}";
                     } else {
                         $newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
                     }
                 } else {
                     $distinct_o = strpos($match_two, "distinct");
                     $up_to_distinct_str = substr($match_two, 0, $distinct_o);
                     //check to see if the distinct is within a function, if so, then proceed as normal
                     if (strpos($up_to_distinct_str, "(")) {
                         //proceed as normal
                         $newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
                     } else {
                         //if distinct is not within a function, then parse
                         //string contains distinct clause, "TOP needs to come after Distinct"
                         //get position of distinct
                         $match_zero = strtolower($matches[0]);
                         $distinct_pos = strpos($match_zero, "distinct");
                         //get position of where
                         $where_pos = strpos($match_zero, "where");
                         //parse through string
                         $beg = substr($matches[0], 0, $distinct_pos + 9);
                         $mid = substr($matches[0], strlen($beg), $where_pos + 5 - strlen($beg));
                         $end = substr($matches[0], strlen($beg) + strlen($mid));
                         //repopulate matches array
                         $matches[1] = $beg;
                         $matches[2] = $mid;
                         $matches[3] = $end;
                         $newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
                     }
                 }
             } else {
                 $orderByMatch = array();
                 preg_match('/^(.*)(\\bORDER BY\\b)(.*)$/is', $matches[3], $orderByMatch);
                 //if there is a distinct clause, parse sql string as we will have to insert the rownumber
                 //for paging, AFTER the distinct clause
                 $grpByStr = '';
                 $hasDistinct = strpos(strtolower($matches[0]), "distinct");
                 require_once 'include/php-sql-parser.php';
                 $parser = new PHPSQLParser();
                 $sqlArray = $parser->parse($sql);
                 if ($hasDistinct) {
                     $matches_sql = strtolower($matches[0]);
                     //remove reference to distinct and select keywords, as we will use a group by instead
                     //we need to use group by because we are introducing rownumber column which would make every row unique
                     //take out the select and distinct from string so we can reuse in group by
                     $dist_str = 'distinct';
                     preg_match('/\\b' . $dist_str . '\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
                     $matches_sql = trim(substr($matches_sql, $matchesPartSQL[0][1] + strlen($dist_str)));
                     //get the position of where and from for further processing
                     preg_match('/\\bfrom\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
                     $from_pos = $matchesPartSQL[0][1];
                     preg_match('/\\where\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
                     $where_pos = $matchesPartSQL[0][1];
                     //split the sql into a string before and after the from clause
                     //we will use the columns being selected to construct the group by clause
                     if ($from_pos > 0) {
                         $distinctSQLARRAY[0] = substr($matches_sql, 0, $from_pos);
                         $distinctSQLARRAY[1] = substr($matches_sql, $from_pos);
                         //get position of order by (if it exists) so we can strip it from the string
                         $ob_pos = strpos($distinctSQLARRAY[1], "order by");
                         if ($ob_pos) {
                             $distinctSQLARRAY[1] = substr($distinctSQLARRAY[1], 0, $ob_pos);
                         }
                         // strip off last closing parentheses from the where clause
                         $distinctSQLARRAY[1] = preg_replace('/\\)\\s$/', ' ', $distinctSQLARRAY[1]);
                     }
                     $grpByStr = array();
                     foreach ($sqlArray['SELECT'] as $record) {
                         if ($record['expr_type'] == 'const') {
                             continue;
                         }
//.........这里部分代码省略.........