Visitar URL original
Add parsing of CASE Expressions by devenbansod · Pull Request #88 · phpmyadmin/sql-parser · GitHub
Skip to content

Add parsing of CASE Expressions #88

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
nijel merged 5 commits into from
Oct 3, 2016
Merged

Add parsing of CASE Expressions #88

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
7f4702a
Add parsing of CASE Expressions
devenbansod Sep 25, 2016
9da8598
Fix some issue with error reports and added testcases
devenbansod Sep 26, 2016
f601f88
Fix declaration to be same as parentClass
devenbansod Sep 26, 2016
30d1710
Remove author tag as per master
devenbansod Sep 26, 2016
f151567
Add more tests
devenbansod Sep 26, 2016
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
250 changes: 250 additions & 0 deletions src/Components/CaseExpression.php
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,250 @@
<?php

/**
* Parses a reference to a CASE expression
*
* @package SqlParser
* @subpackage Components
*/
namespace SqlParser\Components;

use SqlParser\Context;
use SqlParser\Component;
use SqlParser\Parser;
use SqlParser\Token;
use SqlParser\TokensList;


/**
* Parses a reference to a CASE expression
*
* @category Components
* @package SqlParser
* @subpackage Components
* @license http://opensource.org/licenses/GPL-2.0 GNU Public License
*/
class CaseExpression extends Component
{

/**
* The value to be compared
*
* @var Expression
*/
public $value;

/**
* The conditions in WHEN clauses
*
* @var array
*/
public $conditions;

/**
* The results matching with the WHEN clauses
*
* @var array
*/
public $results;

/**
* The values to be compared against
*
* @var array
*/
public $compare_values;

/**
* The result in ELSE section of expr
*
* @var array
*/
public $else_result;

/**
* Constructor.
*
*/
public function __construct()
{
}

/**
*
* @param Parser $parser The parser that serves as context.
* @param TokensList $list The list of tokens that are being parsed.
*
* @return Expression
*/
public static function parse(Parser $parser, TokensList $list, array $options = array())
{
$ret = new CaseExpression();

/**
* State of parser
*
* @var int $parser
*/
$state = 0;

/**
* Syntax type (type 0 or type 1)
*
* @var int $type
*/
$type = 0;

++$list->idx; // Skip 'CASE'

for (; $list->idx < $list->count; ++$list->idx) {

/**
* Token parsed at this moment.
*
* @var Token $token
*/
$token = $list->tokens[$list->idx];

// Skipping whitespaces and comments.
if (($token->type === Token::TYPE_WHITESPACE)
|| ($token->type === Token::TYPE_COMMENT)
) {
continue;
}

if ($state === 0) {
if ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'WHEN'
) {
++$list->idx; // Skip 'WHEN'
$new_condition = Condition::parse($parser, $list);
$type = 1;
$state = 1;
$ret->conditions[] = $new_condition;
} elseif ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'ELSE'
) {
++$list->idx; // Skip 'ELSE'
$ret->else_result = Expression::parse($parser, $list);
$state = 0; // last clause of CASE expression
} elseif ($token->type === Token::TYPE_KEYWORD
&& ($token->value === 'END'
|| $token->value === 'end')
) {
$state = 3; // end of CASE expression
++$list->idx;
break;
} elseif ($token->type === Token::TYPE_KEYWORD) {
$parser->error(__('Unexpected keyword.'), $token);
break;
} else {
$ret->value = Expression::parse($parser, $list);
$type = 0;
$state = 1;
}
} elseif ($state === 1) {
if ($type === 0) {
if ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'WHEN'
) {
++$list->idx; // Skip 'WHEN'
$new_value = Expression::parse($parser, $list);
$state = 2;
$ret->compare_values[] = $new_value;
} elseif ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'ELSE'
) {
++$list->idx; // Skip 'ELSE'
$ret->else_result = Expression::parse($parser, $list);
$state = 0; // last clause of CASE expression
} elseif ($token->type === Token::TYPE_KEYWORD
&& ($token->value === 'END'
|| $token->value === 'end')
) {
$state = 3; // end of CASE expression
++$list->idx;
break;
} elseif ($token->type === Token::TYPE_KEYWORD) {
$parser->error(__('Unexpected keyword.'), $token);
break;
}
} else {
if ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'THEN'
) {
++$list->idx; // Skip 'THEN'
$new_result = Expression::parse($parser, $list);
$state = 0;
$ret->results[] = $new_result;
} elseif ($token->type === Token::TYPE_KEYWORD) {
$parser->error(__('Unexpected keyword.'), $token);
break;
}
}
} elseif ($state === 2) {
if ($type === 0) {
if ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'THEN'
) {
++$list->idx; // Skip 'THEN'
$new_result = Expression::parse($parser, $list);
$ret->results[] = $new_result;
$state = 1;
} elseif ($token->type === Token::TYPE_KEYWORD) {
$parser->error(__('Unexpected keyword.'), $token);
break;
}
}
}
}

if ($state !== 3) {
$parser->error(
__('Unexpected end of CASE expression'),
$list->tokens[$list->idx - 1]
);
}

--$list->idx;
return $ret;
}

/**
* @param Expression $component The component to be built.
* @param array $options Parameters for building.
*
* @return string
*/
public static function build($component, array $options = array())
{
$ret = 'CASE ';
if (isset($component->value)) {
// Syntax type 0
$ret .= $component->value . ' ';
for (
$i = 0;
$i < count($component->compare_values) && $i < count($component->results);
++$i
) {
$ret .= 'WHEN ' . $component->compare_values[$i] . ' ';
$ret .= 'THEN ' . $component->results[$i] . ' ';
}
} else {
// Syntax type 1
for (
$i = 0;
$i < count($component->conditions) && $i < count($component->results);
++$i
) {
$ret .= 'WHEN ' . Condition::build($component->conditions[$i]) . ' ';
$ret .= 'THEN ' . $component->results[$i] . ' ';
}
}
if (isset($component->else_result)) {
$ret .= 'ELSE ' . $component->else_result . ' ';
}
$ret .= 'END';

return $ret;
}
}
14 changes: 11 additions & 3 deletions src/Components/ExpressionArray.php
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,7 @@
<?php

/**
* Parses a a list of expressions delimited by a comma.
* Parses a list of expressions delimited by a comma.
*
* @package SqlParser
* @subpackage Components
Expand All @@ -14,7 +14,7 @@
use SqlParser\TokensList;

/**
* Parses a a list of expressions delimited by a comma.
* Parses a list of expressions delimited by a comma.
*
* @category Keywords
* @package SqlParser
Expand Down Expand Up @@ -73,13 +73,21 @@ public static function parse(Parser $parser, TokensList $list, array $options =
&& ((~$token->flags & Token::FLAG_KEYWORD_FUNCTION))
&& ($token->value !== 'DUAL')
&& ($token->value !== 'NULL')
&& ($token->value !== 'CASE')
) {
// No keyword is expected.
break;
}

if ($state === 0) {
$expr = Expression::parse($parser, $list, $options);
if ($token->type === Token::TYPE_KEYWORD
&& $token->value === 'CASE'
) {
$expr = CaseExpression::parse($parser, $list, $options);
} else {
$expr = Expression::parse($parser, $list, $options);
}

if ($expr === null) {
break;
}
Expand Down
88 changes: 88 additions & 0 deletions tests/Components/CaseExpressionTest.php
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,88 @@
<?php

namespace SqlParser\Tests\Components;

use SqlParser\Parser;
use SqlParser\Components\CaseExpression;

use SqlParser\Tests\TestCase;

class CaseExpressionTest extends TestCase
{

public function testParseBuild()
{
$caseExprQuery = 'case 1 when 1 then "Some" else "Other" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE 1 WHEN 1 THEN "Some" ELSE "Other" END'
);
}

public function testParseBuild2()
{
$caseExprQuery = 'case when 1=1 then "India" else "Other" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE WHEN 1=1 THEN "India" ELSE "Other" END'
);
}

public function testParseBuild3()
{
$caseExprQuery = 'case 1 when 1 then "Some" '
. 'when 2 then "SomeOther" else "Other" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE 1 WHEN 1 THEN "Some" WHEN 2 THEN "SomeOther" ELSE "Other" END'
);
}

public function testParseBuild4()
{
$caseExprQuery = 'case 1 when 1 then "Some" '
. 'when 2 then "SomeOther" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE 1 WHEN 1 THEN "Some" WHEN 2 THEN "SomeOther" END'
);
}

public function testParseBuild5()
{
$caseExprQuery = 'case when 1=1 then "Some" '
. 'when 1=2 then "SomeOther" else "Other" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE WHEN 1=1 THEN "Some" WHEN 1=2 THEN "SomeOther" ELSE "Other" END'
);
}

public function testParseBuild6()
{
$caseExprQuery = 'case when 1=1 then "Some" '
. 'when 1=2 then "SomeOther" end';
$component = CaseExpression::parse(
new Parser(),
$this->getTokensList($caseExprQuery));
$this->assertEquals(
CaseExpression::build($component),
'CASE WHEN 1=1 THEN "Some" WHEN 1=2 THEN "SomeOther" END'
);
}
}
9 changes: 9 additions & 0 deletions tests/Parser/SelectStatementTest.php
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -33,6 +33,15 @@ public function testSelectProvider()
array('parser/parseSelect4'),
array('parser/parseSelectErr1'),
array('parser/parseSelectNested'),
array('parser/parseSelectCase1'),
array('parser/parseSelectCase2'),
array('parser/parseSelectCase3'),
array('parser/parseSelectCase4'),
array('parser/parseSelectCaseErr1'),
array('parser/parseSelectCaseErr2'),
array('parser/parseSelectCaseErr3'),
array('parser/parseSelectCaseErr4'),
array('parser/parseSelectCaseErr5'),
);
}
}
Loading