summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/Fragments/WhereKeyword.php55
-rw-r--r--src/Utils/Query.php149
-rw-r--r--tests/Utils/QueryTest.php84
3 files changed, 253 insertions, 35 deletions
diff --git a/src/Fragments/WhereKeyword.php b/src/Fragments/WhereKeyword.php
index f5bec24..4ce63a4 100644
--- a/src/Fragments/WhereKeyword.php
+++ b/src/Fragments/WhereKeyword.php
@@ -26,11 +26,20 @@ class WhereKeyword extends Fragment
{
/**
- * Logical operators that can be used to chain expressions.
+ * Logical operators that can be used to delimit expressions.
*
* @var array
*/
- public static $OPERATORS = array('&&', 'AND', 'OR', 'XOR', '||');
+ public static $DELIMITERS = array('&&', 'AND', 'OR', 'XOR', '||');
+
+ /**
+ * Hashmap containing reserved keywords that are also operators.
+ *
+ * @var array
+ */
+ public static $OPERATORS = array('AND' => 1, 'BETWEEN' => 1, 'LIKE' => 1,
+ 'OR' => 1, 'XOR' => 1
+ );
/**
* Identifiers recognized.
@@ -82,6 +91,15 @@ class WhereKeyword extends Fragment
*/
$brackets = 0;
+ /**
+ * Whether there was a `BETWEEN` keyword before or not.
+ * It is required to keep track of them because their structure contains
+ * the keyword `AND`, which is also an operator that delimits
+ * expressions.
+ * @var bool
+ */
+ $betweenBefore = false;
+
for (; $list->idx < $list->count; ++$list->idx) {
/**
@@ -101,20 +119,24 @@ class WhereKeyword extends Fragment
}
// Conditions are delimited by logical operators.
- if (in_array($token->value, static::$OPERATORS, true)) {
- $expr->expr = trim($expr->expr);
- if (!empty($expr->expr)) {
- // Adding the condition that is delimited by this operator.
+ if (in_array($token->value, static::$DELIMITERS, true)) {
+ if (($betweenBefore) && ($token->value === 'AND')) {
+ $betweenBefore = false;
+ } else {
+ $expr->expr = trim($expr->expr);
+ if (!empty($expr->expr)) {
+ // Adding the condition that is delimited by this operator.
+ $ret[] = $expr;
+ }
+
+ // Adding the operator.
+ $expr = new WhereKeyword($token->value);
+ $expr->isOperator = true;
$ret[] = $expr;
- }
-
- // Adding the operator.
- $expr = new WhereKeyword($token->value);
- $expr->isOperator = true;
- $ret[] = $expr;
- $expr = new WhereKeyword();
- continue;
+ $expr = new WhereKeyword();
+ continue;
+ }
}
if ($token->type === Token::TYPE_OPERATOR) {
@@ -127,7 +149,10 @@ class WhereKeyword extends Fragment
// No keyword is expected.
if (($token->type === Token::TYPE_KEYWORD) && ($token->flags & Token::FLAG_KEYWORD_RESERVED)) {
- if ($brackets == 0) {
+ if ($token->value === 'BETWEEN') {
+ $betweenBefore = true;
+ }
+ if (($brackets === 0) && (empty(static::$OPERATORS[$token->value]))) {
break;
}
}
diff --git a/src/Utils/Query.php b/src/Utils/Query.php
index f741620..5c4553d 100644
--- a/src/Utils/Query.php
+++ b/src/Utils/Query.php
@@ -354,7 +354,15 @@ class Query
*
* @param string $query The query to be parsed.
*
- * @return array
+ * @return array The array returned is the one returned by
+ * `static::getFlags()`, with the following keys added:
+ * - parser - the parser used to analyze the query;
+ * - statement - the first statement resulted from parsing;
+ * - select_tables - the real name of the tables selected;
+ * if there are no table names in the `SELECT`
+ * expressions, the table names are fetched from the
+ * `FROM` expressions
+ * - select_expr - selected expressions
*/
public static function getAll($query)
{
@@ -375,15 +383,33 @@ class Query
$ret['select_tables'] = array();
$ret['select_expr'] = array();
+ // Finding tables' aliases and their associated real names.
+ $tableAliases = array();
+ foreach ($statement->from as $expr) {
+ if ((!empty($expr->table)) && (!empty($expr->alias))) {
+ $tableAliases[$expr->alias] = array(
+ $expr->table,
+ !empty($expr->database) ? $expr->database : null
+ );
+ }
+ }
+
// Trying to find selected tables only from the select expression.
// Sometimes, this is not possible because the tables aren't defined
// explicitly (e.g. SELECT * FROM film, SELECT film_id FROM film).
foreach ($statement->expr as $expr) {
if (!empty($expr->table)) {
- $ret['select_tables'][] = array(
- $expr->table,
- !empty($expr->database) ? $expr->database : null
- );
+ if (empty($tableAliases[$expr->table])) {
+ $arr = array(
+ $expr->table,
+ !empty($expr->database) ? $expr->database : null
+ );
+ } else {
+ $arr = $tableAliases[$expr->table];
+ }
+ if (!in_array($arr, $ret['select_tables'])) {
+ $ret['select_tables'][] = $arr;
+ }
} else {
$ret['select_expr'][] = $expr->expr;
}
@@ -391,14 +417,17 @@ class Query
// If no tables names were found in the SELECT clause or if there
// are expressions like * or COUNT(*), etc. tables names should be
- // extracted from the FROM clause as well.
- if ((empty($ret['select_tables'])) || (!$ret['select_expr'])) {
+ // extracted from the FROM clause.
+ if (empty($ret['select_tables'])) {
foreach ($statement->from as $expr) {
if (!empty($expr->table)) {
- $ret['select_tables'][] = array(
+ $arr = array(
$expr->table,
!empty($expr->database) ? $expr->database : null
);
+ if (!in_array($arr, $ret['select_tables'])) {
+ $ret['select_tables'][] = $arr;
+ }
}
}
}
@@ -413,10 +442,13 @@ class Query
* @param Statement $statement The parsed query that has to be modified.
* @param TokensList $list The list of tokens.
* @param string $clause The clause to be returned.
- * @param int $type The type of the search.
- * -1 for everything that was before
- * 0 only for the clause
- * 1 for everything after
+ * @param int|string $type The type of the search.
+ * If int,
+ * -1 for everything that was before
+ * 0 only for the clause
+ * 1 for everything after
+ * If string, the name of the first clause that
+ * shouldn't be included.
* @param bool $skipFirst Whether to skip the first keyword in clause.
*
* @return string
@@ -467,6 +499,32 @@ class Query
*/
$clauseIdx = $clauses[$clauseType];
+ $firstClauseIdx = $clauseIdx;
+
+ $lastClauseIdx = $clauseIdx + 1;
+
+ // Determining the behaviour of this function.
+ if ($type === -1) {
+ $firstClauseIdx = -1; // Something small enough.
+ $lastClauseIdx = $clauseIdx - 1;
+ } elseif ($type === 1) {
+ $firstClauseIdx = $clauseIdx + 1;
+ $lastClauseIdx = 10000; // Something big enough.
+ } elseif (is_string($type)) {
+ if ($clauses[$type] > $clauseIdx) {
+ $firstClauseIdx = $clauseIdx + 1;
+ $lastClauseIdx = $clauses[$type] - 1 ;
+ } else {
+ $firstClauseIdx = $clauses[$type] + 1;
+ $lastClauseIdx = $clauseIdx - 1 ;
+ }
+ }
+
+ // This option is unavailable for multiple clauses.
+ if ($type !== 0) {
+ $skipFirst = false;
+ }
+
for ($i = $statement->first; $i <= $statement->last; ++$i) {
$token = $list->tokens[$i];
@@ -493,10 +551,7 @@ class Query
}
}
- if ((($type === -1) && ($currIdx < $clauseIdx))
- || (($type === 0) && ($currIdx === $clauseIdx))
- || (($type === 1) && ($currIdx > $clauseIdx))
- ) {
+ if (($firstClauseIdx <= $currIdx) && ($currIdx <= $lastClauseIdx)) {
$ret .= $token->token;
}
}
@@ -538,4 +593,66 @@ class Query
return static::getClause($statement, $list, $old, -1, false) . ' ' .
$new . ' ' . static::getClause($statement, $list, $old, 1, false);
}
+
+ /**
+ * Builds a query by rebuilding the statement from the tokens list supplied
+ * and replaces multiple clauses.
+ *
+ * @param Statement $statement The parsed query that has to be modified.
+ * @param TokensList $list The list of tokens.
+ * @param array $ops Clauses to be replaced. Contains multiple
+ * arrays having two values: array($old, $new).
+ * Clauses must be sorted.
+ *
+ * @return string
+ */
+ public static function replaceClauses($statement, $list, array $ops)
+ {
+ $count = count($ops);
+
+ // Nothing to do.
+ if ($count === 0) {
+ return '';
+ }
+
+ /**
+ * Value to be returned.
+ * @var string
+ */
+ $ret = '';
+
+ /**
+ * The clauses of this type of statement and their index.
+ * @var array
+ */
+ $clauses = array_keys($statement::$CLAUSES);
+
+ // If there is only one clause, `replaceClause()` should be used.
+ if ($count === 1) {
+ return static::replaceClause(
+ $statement,
+ $list,
+ $ops[0][0],
+ $ops[0][1]
+ );
+ }
+
+ // Adding everything before first replacement.
+ $ret .= static::getClause($statement, $list, $ops[0][0], -1) . ' ';
+
+ // Doing replacements.
+ for ($i = 0; $i < $count; ++$i) {
+ $ret .= $ops[$i][1] . ' ';
+
+ // Adding everything between this and next replacement.
+ if ($i + 1 !== $count) {
+ $ret .= static::getClause($statement, $list, $ops[$i][0], $ops[$i + 1][0], -1) . ' ';
+ }
+ }
+
+ // Adding everything after the last replacement.
+ $ret .= static::getClause($statement, $list, $ops[$count - 1][0], 1);
+
+ return $ret;
+ }
}
diff --git a/tests/Utils/QueryTest.php b/tests/Utils/QueryTest.php
index 918c5b5..2cd3df7 100644
--- a/tests/Utils/QueryTest.php
+++ b/tests/Utils/QueryTest.php
@@ -251,6 +251,8 @@ class QueryTest extends TestCase
public function testGetAll()
{
+ $this->assertEquals(array(), Query::getAll(''));
+
$query = 'SELECT *, actor.actor_id, sakila2.film.*
FROM sakila2.city, sakila2.film, actor';
$parser = new Parser($query);
@@ -263,7 +265,7 @@ class QueryTest extends TestCase
'select_expr' => array('*'),
'select_tables' => array(
array('actor', null),
- array('film', 'sakila2')
+ array('film', 'sakila2'),
)
)
),
@@ -281,7 +283,24 @@ class QueryTest extends TestCase
'select_expr' => array('*'),
'select_tables' => array(
array('actor', 'sakila'),
- array('film', null)
+ array('film', null),
+ )
+ )
+ ),
+ Query::getAll($query)
+ );
+
+ $query = 'SELECT a.actor_id FROM sakila.actor AS a, film';
+ $parser = new Parser($query);
+ $this->assertEquals(
+ array_merge(
+ Query::getFlags($parser->statements[0], true),
+ array(
+ 'parser' => $parser,
+ 'statement' => $parser->statements[0],
+ 'select_expr' => array(),
+ 'select_tables' => array(
+ array('actor', 'sakila'),
)
)
),
@@ -289,9 +308,24 @@ class QueryTest extends TestCase
);
}
- public function testGetAllEmpty()
+ public function testGetClause()
{
- $this->assertEquals(array(), Query::getAll(''));
+ $parser = new Parser(
+ 'SELECT c.city_id, c.country_id ' .
+ 'FROM `city` ' .
+ 'WHERE city_id < 1 ' .
+ 'ORDER BY city_id ASC ' .
+ 'LIMIT 0, 1 ' .
+ 'INTO OUTFILE "/dev/null"'
+ );
+ $this->assertEquals(
+ 'WHERE city_id < 1 ORDER BY city_id ASC',
+ Query::getClause(
+ $parser->statements[0],
+ $parser->list,
+ 'LIMIT', 'FROM'
+ )
+ );
}
public function testReplaceClause()
@@ -321,4 +355,46 @@ class QueryTest extends TestCase
)
);
}
+
+ public function testRepalceClauses()
+ {
+ $this->assertEquals('', Query::replaceClauses(null, null, array()));
+
+ $parser = new Parser('SELECT *, (SELECT 1) FROM film LIMIT 0, 10;');
+ $this->assertEquals(
+ 'SELECT *, (SELECT 1) FROM film WHERE film_id > 0 LIMIT 0, 10',
+ Query::replaceClauses(
+ $parser->statements[0],
+ $parser->list,
+ array(
+ array('WHERE', 'WHERE film_id > 0'),
+ )
+ )
+ );
+
+ $parser = new Parser(
+ 'SELECT c.city_id, c.country_id ' .
+ 'FROM `city` ' .
+ 'WHERE city_id < 1 ' .
+ 'ORDER BY city_id ASC ' .
+ 'LIMIT 0, 1 ' .
+ 'INTO OUTFILE "/dev/null"'
+ );
+ $this->assertEquals(
+ 'SELECT c.city_id, c.country_id ' .
+ 'FROM city AS c ' .
+ 'ORDER BY city_id ASC ' .
+ 'LIMIT 0, 10 ' .
+ 'INTO OUTFILE "/dev/null"',
+ Query::replaceClauses(
+ $parser->statements[0],
+ $parser->list,
+ array(
+ array('FROM', 'FROM city AS c'),
+ array('WHERE', ''),
+ array('LIMIT', 'LIMIT 0, 10'),
+ )
+ )
+ );
+ }
}
generated by cgit v1.1 at 2025-05-11 23:15:18 +0000