public function testIssue56()
{
// optimizer/index hints
// TODO: not solved
$parser = new PHPSQLParser();
$sql = "insert /* +APPEND */ into TableName (Col1,col2) values(1,'pol')";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue56a.serialized');
$this->assertEquals($expected, $p, 'optimizer hint within INSERT');
// optimizer/index hints
// TODO: not solved
$parser = new PHPSQLParser();
$sql = "insert /* a comment -- haha */ into TableName (Col1,col2) values(1,'pol')";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue56a.serialized');
$this->assertEquals($expected, $p, 'multiline comment with inline comment inside');
// inline comment
// TODO: not solved
$sql = "SELECT acol -- an inline comment\n FROM --another comment\n table\n WHERE x = 1";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue56b.serialized');
$this->assertEquals($expected, $p, 'inline comment should not fail, issue 56');
// inline comment
// TODO: not solved
$sql = "SELECT acol -- an /*inline comment\n FROM --another */comment\n table\n WHERE x = 1";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue56b.serialized');
$this->assertEquals($expected, $p, 'inline comment with multiline comment inside');
}
public function testMixedQuotes()
{
$parser = new PHPSQLParser();
$sql = 'SELECT ISNULL(\'"\') AS foo FROM bar;';
$parser->parse($sql);
$this->assertEquals('\'"\'', $parser->parsed['SELECT'][0]['sub_tree'][0]['base_expr'], "Mixed quotes test failed");
}
/**
* @throws \Exception
*
* @return string
*/
public function convert()
{
$parsed = $this->sqlParser->parse($this->sql);
if (false === $parsed) {
throw new \Exception('SQL query is not valid');
}
$results = [];
foreach ($parsed as $section => $data) {
if ($this->converterFactory->canCreate($section)) {
$converter = $this->converterFactory->create($section);
$results = array_merge($results, $converter->convert($data));
}
}
$table = current(array_filter($results, function ($item) {
return 'table' === $item['name'];
}));
unset($results[array_search($table, $results)]);
array_unshift($results, $table);
foreach ($results as $function) {
$args = isset($function['args']) ? $function['args'] : [];
$this->generator->addFunction($function['name'], $args);
}
$this->generator->addFunction('get');
return $this->generator->generate();
}
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();
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'union3.serialized');
$this->assertEquals($expected, $p, 'complicated mysql union');
}
public function testIssue15()
{
$parser = new PHPSQLParser();
$sql = "select usr_id, usr_login, case id_tipousuario when 1 then 'Usuario CVE' when 2 then concat('Usuario Vendedor -', codigovendedor, '-') when 3 then concat('Usuario Vendedor Meson (', codigovendedor, ')') end tipousuario, CONCAT( usr_nombres, ' ', usr_apellidos ) as nom_com, cod_local from usuarios where usr_estado <> 2 order by 3, 1, 4";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue15.serialized');
$this->assertEquals($expected, $p, 'parenthesis problem on issue 15');
}
public function testIssue90()
{
$sql = 'INSERT DELAYED IGNORE INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;';
$parser = new PHPSQLParser();
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue90.serialized');
$this->assertEquals($expected, $p, 'on duplicate key problem');
}
public function testIssue25()
{
$parser = new PHPSQLParser();
$sql = "SELECT * FROM contacts WHERE contacts.id IN (SELECT email_addr_bean_rel.bean_id FROM email_addr_bean_rel, email_addresses WHERE email_addresses.id = email_addr_bean_rel.email_address_id AND email_addr_bean_rel.deleted = 0 AND email_addr_bean_rel.bean_module = 'Contacts' AND email_addresses.email_address IN (\"test@example.com\"))";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue25.serialized');
$this->assertEquals($expected, $p, 'parenthesis problem on issue 25');
}
public function testInlist()
{
$parser = new PHPSQLParser();
$sql = "SELECT q.qid, question, gid FROM questions as q WHERE (select count(*) from answers as a where a.qid=q.qid and scale_id=0)=0 and sid=11929 AND type IN ('F', 'H', 'W', 'Z', '1') and q.parent_qid=0";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'inlist1.serialized');
$this->assertEquals($expected, $p, 'in list within WHERE clause');
}
public function testIssueGit11()
{
$query = "select column from table as ";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git11.serialized');
$this->assertEquals($expected, $p, 'infinite loop with empty alias');
}
public function testIssue91()
{
$sql = 'SELECT DISTINCT colA * colB From test t';
$parser = new PHPSQLParser();
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue91.serialized');
$this->assertEquals($expected, $p, 'distinct select');
}
public function testIssue94()
{
$sql = 'SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH) AS next_month';
$parser = new PHPSQLParser();
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'issue94.serialized');
$this->assertEquals($expected, $p, 'date_add()');
}
public function testDrop()
{
$parser = new PHPSQLParser();
$sql = "drop table if exists xyz cascade";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'drop.serialized');
$this->assertEquals($expected, $p, 'drop table statement');
}
public function testIssue12()
{
$parser = new PHPSQLParser();
$sql = "SELECT SQL_CALC_FOUND_ROWS SmTable.*, MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles' WITH QUERY EXPANSION) AS keyword_score FROM SmTable WHERE SmTable.status = 'A' AND (SmTable.country_id = 1 AND SmTable.state_id = 10) AND MATCH (SmTable.fulltextsearch_keyword) AGAINST ('google googles') ORDER BY SmTable.level DESC, keyword_score DESC LIMIT 0,10";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue12.serialized');
$this->assertEquals($expected, $p, 'issue 12');
}
public function testIssue51()
{
$parser = new PHPSQLParser();
$sql = "SELECT CAST( 12 AS decimal( 9, 3 ) )";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue51.serialized');
$this->assertEquals($expected, $p, 'should not die if query contains cast expression');
}
public function testIssue41()
{
$parser = new PHPSQLParser();
$sql = "SELECT * FROM v\$mytable";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue41.serialized');
$this->assertEquals($expected, $p, 'escaped $ in tablename');
}
public function testNested2()
{
$parser = new PHPSQLParser();
$sql = "SELECT * FROM t1 LEFT JOIN (t2, t3, t4)\n ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'nested2.serialized');
$this->assertEquals($expected, $p, 'left joins with multiple tables');
}
public function testIssue45()
{
$parser = new PHPSQLParser();
$sql = 'SELECT a from b left join c on c.a = b.a and (c.b. = b.b) where a.a > 1';
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue45.serialized');
$this->assertEquals($expected, $p, 'issue 45 position problem');
}
public function testIssue32()
{
$parser = new PHPSQLParser();
$sql = "UPDATE user SET lastlogin = 7, x = 3";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue32.serialized');
$this->assertEquals($expected, $p, 'update with keyword user as table');
}
public function testIssue30()
{
$parser = new PHPSQLParser();
$sql = "SELECT foo.a FROM test foo WHERE RIGHT(REPLACE(foo.bar,'(0',''),7) = 'a'";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue30.serialized');
$this->assertEquals($expected, $p, 'parenthesis within string literals within function parameter list');
}
public function testIssue42()
{
$parser = new PHPSQLParser();
$sql = "SELECT 'a string with an escaped quote \\' in it' AS some_alias FROM some_table";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue42.serialized');
$this->assertEquals($expected, $p, 'escaped quote in string constant');
}
public function testIssue37()
{
$parser = new PHPSQLParser();
$sql = "INSERT INTO test (`name`, `test`) VALUES ('Hello this is what happens\n when new lines are involved', '')";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue37.serialized');
$this->assertEquals($expected, $p, 'INSERT statement with newline character');
}
public function testIssue43()
{
$parser = new PHPSQLParser();
$sql = "SELECT title, introtext\n FROM kj9un_content\n WHERE `id`='159'";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue43.serialized');
$this->assertEquals($expected, $p, 'problem with linefeed after tablename');
}
public function testIssue38()
{
$sql = "SELECT * FROM `table` `t` WHERE ( ( UNIX_TIMESTAMP() + 3600 ) > `t`.`expires` ) ";
$parser = new PHPSQLParser();
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue38.serialized');
$this->assertEquals($expected, $p, 'function within WHERE and quoted table + quoted columns');
}
public function testIssue82()
{
$parser = new PHPSQLParser();
$sql = "SELECT SUM(1) * 100 as number";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue82.serialized');
$this->assertEquals($expected, $p, 'operator * problem');
}
public function testIssue11()
{
$test = str_repeat('0', 18000);
$query = "UPDATE club SET logo='{$test}' WHERE id=1";
$parser = new PHPSQLParser();
$p = $parser->parse($query);
$expected = getExpectedValue(dirname(__FILE__), 'issue11.serialized');
$this->assertEquals($expected, $p, 'very long statement');
}
public function testIssue39()
{
$parser = new PHPSQLParser();
$sql = "SELECT COUNT(DISTINCT bla) FROM foo";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue39.serialized');
$this->assertEquals($expected, $p, 'count(distinct x)');
}
public function testIssue52()
{
$parser = new PHPSQLParser();
$sql = "SELECT a FROM b WHERE c IN (1, 2)";
$parser->parse($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue52.serialized');
$this->assertEquals($expected, $p, 'should not die if query contains IN clause');
}
public function testIssue31()
{
$parser = new PHPSQLParser();
$sql = "SELECT\tsp.level,\n \t\tCASE sp.level\n \t\t\tWHEN 'bronze' THEN 0\n \t\t\tWHEN 'silver' THEN 1\n \t\t\tWHEN 'gold' THEN 2\n \t\t\tELSE -1\n \t\tEND AS levelnum,\n \t\tsp.alt_en,\n \t\tsp.alt_pl,\n \t\tDATE_FORMAT(sp.vu_start,'%Y-%m-%d %T') AS vu_start,\n \t\tDATE_FORMAT(sp.vu_stop,'%Y-%m-%d %T') AS vu_stop,\n \t\tABS(TO_DAYS(now()) - TO_DAYS(sp.vu_start)) AS frdays,\n \t\tABS(TO_DAYS(now()) - TO_DAYS(sp.vu_stop)) AS todays,\n \t\tIF(ISNULL(TO_DAYS(sp.vu_start)) OR ISNULL(TO_DAYS(sp.vu_stop))\n \t\t\t, 1\n \t\t\t, IF(TO_DAYS(now()) < TO_DAYS(sp.vu_start)\n \t\t\t\t, TO_DAYS(now()) - TO_DAYS(sp.vu_start)\n \t\t\t\t, IF(TO_DAYS(now()) > TO_DAYS(sp.vu_stop)\n \t\t\t\t\t, TO_DAYS(now()) - TO_DAYS(sp.vu_stop)\n \t\t\t\t\t, 0))) AS status,\n \t\tst.id,\n \t\tSUM(IF(st.type='view',1,0)) AS view,\n \t\tSUM(IF(st.type='click',1,0)) AS click\n FROM\tstats AS st,\n \t\tsponsor AS sp\n WHERE\tst.id=sp.id\n GROUP BY st.id\n ORDER BY sp.alt_en asc, sp.alt_pl asc";
$parser->parse($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue31.serialized');
$this->assertEquals($expected, $p, 'very complex statement with keyword view as alias');
}
public function testIssueGit183()
{
$query = "SELECT *\nFROM SC_CATALOG_DETAIL_REG CD\nINNER JOIN MASTER_ITEM_LOOKUP IL\nON to_number( CD.STYLE ) = to_number( IL.SKU_NUM )\n\t\t\t\t";
$parser = new PHPSQLParser();
$parser->addCustomFunction("to_number");
$p = $parser->parse($query, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git183.serialized');
$this->assertEquals($expected, $p, "Oracle\\'s to_number");
}
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');
}