public function testIssue101()
{
$sql = "SELECT tab.col AS `tab.col`, tab2.col AS `tab2.col` FROM tab, tab2";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue101.sql', false);
$this->assertSame($expected, $created, 'alias with quotes');
}
public function testIssue118()
{
$query = "select organism_name as reference from organisms group by reference";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue118.sql', false);
$this->assertSame($expected, $created, 'alias within group by');
}
public function testIssue127()
{
$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);
$this->assertSame($expected, $created, 'unary operator');
}
public function testIssue121()
{
$query = "CREATE TABLE t (mv DECIMAL(3) DEFAULT 10)";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue121.sql', false);
$this->assertSame($expected, $created, 'create table with default value');
}
public function testIssue141()
{
$query = "SELECT f FROM t ORDER BY (f-0.0)";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue141.sql', false);
$this->assertSame($expected, $created, 'bracket expressions within order-by');
}
public function testIssue130()
{
$query = "select * from t1 order by c2-c1";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue130.sql', false);
$this->assertSame($expected, $created, 'expressions within the ORDER-BY clause');
}
public function testIssue134()
{
$query = "select t1.*\n from Table1 t1\n STRAIGHT_JOIN Table2 t2\n on t1.CommonID = t2.CommonID\n where t1.FilterID = 1";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue134.sql', false);
$this->assertSame($expected, $created, 'a straight_join within from clause');
}
public function testMagnus()
{
$sql = "SELECT\n u.`id` AS userid,\n u.`user` AS username,\n u.`firstname`,\n u.`lastname`,\n u.`email`,\n CONCAT(19, lastname, 2013) AS test\n FROM\n `user` u\n ORDER BY\n u.`user` DESC";
$parser = new PHPSQLParser();
$parsed = $parser->parse($sql);
$creator = new PHPSQLCreator();
$created = $creator->create($parsed);
$expected = getExpectedValue(dirname(__FILE__), 'magnus.sql', false);
$this->assertSame($expected, $created, 'Aliases for functions.');
}
public function testIssue126()
{
$query = "DELETE FROM t1";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue126.sql', false);
$this->assertSame($expected, $created, 'delete statement');
}
public function testIssue147()
{
$query = "SELECT f FROM t WHERE x in (Select x from y)";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue147.sql', false);
$this->assertSame($expected, $created, 'subqueries within WHERE clause');
}
public function testIssue123()
{
$query = "CREATE TABLE t1 (c1 CHAR(3),c2 CHAR(3),KEY(c1))";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue123.sql', false);
$this->assertSame($expected, $created, 'create table with key column');
}
public function testUnion3()
{
$sql = "SELECT x FROM ((SELECT y FROM z WHERE (y > 2) ) UNION ALL (SELECT a FROM z WHERE (y < 2))) as f ";
$parser = new PHPSQLParser();
$creator = new PHPSQLCreator();
$parsed = $parser->parse($sql);
$created = $creator->create($parsed);
$expected = getExpectedValue(dirname(__FILE__), 'union3.sql', false);
$this->assertEquals($expected, $created, 'complicated mysql union');
}
public function testIssue131()
{
$query = "create unique index i1 using BTREE on t1 (c1(5) DESC, `col 2`(8) ASC) ALGORITHM=DEFAULT using hash LOCK=SHARED";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue131.sql', false);
$this->assertSame($expected, $created, 'CREATE INDEX statement');
}
public function testIssue144()
{
$query = "SELECT * FROM TableA JOIN TableB USING(Col1, Col2)";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue144.sql', false);
$this->assertSame($expected, $created, 'missing commas in Ref clause');
}
public function testIssue129()
{
$query = "DROP TEMPORARY TABLE IF EXISTS t1, t2 CASCADE";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue129.sql', false);
$this->assertSame($expected, $created, 'drop table should not fail');
}
public function testIssue100()
{
$query = "SELECT 0 AS Zero FROM table";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue100.sql', false);
$this->assertSame($expected, $created, 'lost alias for constants');
}
public function testIssue117()
{
$sql = "(SELECT x FROM table) ORDER BY x";
$parser = new PHPSQLParser();
$p = $parser->parse($sql);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue117.sql', false);
$this->assertSame($expected, $created, 'parentheses on the first position of statement');
}
public function testIssue132()
{
$query = "select (c1 - c2) AS c3 from t1";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue132.sql', false);
$this->assertSame($expected, $created, 'lost alias of expression');
}
public function testIssue124()
{
$query = "SELECT t1.c1, t2.c2 FROM t1 LEFT JOIN t2 ON (LEFT(t1.c2,6) = t2.c1)";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue124.sql', false);
$this->assertSame($expected, $created, 'ref clause with function');
}
public function testIssueGit10()
{
$query = "SELECT\nREPLACE( f.web_program,'\n', '' ) AS web_program,\nid AS change_id\nFROM\nfile f\nHAVING\nchange_id > :change_id";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$creator = new PHPSQLCreator();
$created = $creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git10.sql', false);
$this->assertSame($expected, $created, 'alias references should work in HAVING clauses');
}