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 testIssue78()
{
$sql = "show columns from `foo.bar`";
$parser = new PHPSQLParser($sql);
$creator = new PHPSQLCreator($parser->parsed);
$created = $creator->created;
$expected = getExpectedValue(dirname(__FILE__), 'issue78a.sql', false);
$this->assertSame($expected, $created, 'show columns from');
$sql = "show CREATE DATABASE `foo`";
$parser = new PHPSQLParser($sql);
$creator = new PHPSQLCreator($parser->parsed);
$created = $creator->created;
$expected = getExpectedValue(dirname(__FILE__), 'issue78b.sql', false);
$this->assertSame($expected, $created, 'show create database');
$sql = "show DATABASES LIKE '%bar%'";
$parser = new PHPSQLParser($sql, true);
$creator = new PHPSQLCreator($parser->parsed);
$created = $creator->created;
$expected = getExpectedValue(dirname(__FILE__), 'issue78c.sql', false);
$this->assertSame($expected, $created, 'show databases like');
$sql = "SHOW ENGINE foo STATUS";
$parser = new PHPSQLParser($sql, true);
$creator = new PHPSQLCreator($parser->parsed);
$created = $creator->created;
$expected = getExpectedValue(dirname(__FILE__), 'issue78d.sql', false);
$this->assertSame($expected, $created, 'show engine status');
$sql = "SHOW FULL COLUMNS FROM `foo.bar` FROM hohoho LIKE '%xmas%'";
$parser = new PHPSQLParser($sql, true);
$creator = new PHPSQLCreator($parser->parsed);
$created = $creator->created;
$expected = getExpectedValue(dirname(__FILE__), 'issue78e.sql', false);
$this->assertSame($expected, $created, 'show full columns from like');
}
public function testSelect()
{
$parser = new PHPSQLParser();
$sql = 'SELECT
1';
$p = $parser->parse($sql);
$this->assertEquals(1, count($p));
$this->assertEquals(1, count($p['SELECT']));
$this->assertEquals('const', $p['SELECT'][0]['expr_type']);
$this->assertEquals('1', $p['SELECT'][0]['base_expr']);
$this->assertEquals('', $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;
$this->assertEquals(3, count($p));
$this->assertEquals(8, count($p['SELECT']));
$this->assertEquals('terminate', $p['SELECT'][count($p['SELECT']) - 1]['base_expr']);
$this->assertEquals(3, count($p));
$this->assertEquals(1, count($p['FROM']));
$this->assertEquals(3, count($p['WHERE']));
$parser->parse('SELECT NOW( ),now(),sysdate( ),sysdate () as now');
$this->assertEquals('sysdate', $parser->parsed['SELECT'][3]['base_expr']);
$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');
$this->assertEquals($expected, $p, '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;
$expected = getExpectedValue(dirname(__FILE__), 'select2.serialized');
$this->assertEquals($expected, $p, 'select for update');
}
public function testIssue74()
{
$parser = new PHPSQLParser();
// DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
$sql = "DROP DATABASE blah";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74a.serialized');
$this->assertEquals($expected, $p, 'drop database statement');
$sql = "DROP SCHEMA blah";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74b.serialized');
$this->assertEquals($expected, $p, 'drop schema statement');
$sql = "DROP DATABASE IF EXISTS blah";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74c.serialized');
$this->assertEquals($expected, $p, 'drop database if exists statement');
$sql = "DROP SCHEMA IF EXISTS blah";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74d.serialized');
$this->assertEquals($expected, $p, 'drop schema if exists statement');
// DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]
$sql = "DROP TABLE blah1, blah2 RESTRICT";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74e.serialized');
$this->assertEquals($expected, $p, 'drop table-list statement');
$sql = "DROP TEMPORARY TABLE IF EXISTS blah1, blah2 CASCADE";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'issue74f.serialized');
$this->assertEquals($expected, $p, 'drop temporary table-list if exists statement');
}
public function testIssueGit24()
{
$query = "SELECT * FROM table WHERE id IN(0,-1,-2,-3)";
$p = $this->parser->parse($query);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git24.serialized');
$this->assertEquals($expected, $p, 'negative integers within IN()');
}
public function testShow()
{
$sql = "show columns from `foo.bar`";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'show1.serialized');
$this->assertEquals($expected, $p, 'show columns from');
$sql = "show CREATE DATABASE `foo`";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'show2.serialized');
$this->assertEquals($expected, $p, 'show create database');
$sql = "show DATABASES LIKE '%bar%'";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'show3.serialized');
$this->assertEquals($expected, $p, 'show databases like');
$sql = "SHOW ENGINE foo STATUS";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'show4.serialized');
$this->assertEquals($expected, $p, 'show engine status');
$sql = "SHOW FULL COLUMNS FROM `foo.bar` FROM hohoho LIKE '%xmas%'";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'show5.serialized');
$this->assertEquals($expected, $p, 'show full columns from like');
}
public function testAlias4()
{
$sql = 'SELECT (select colA AS a from testA) + (select colB b from testB) From tableC x';
$p = $this->parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'alias4.serialized');
$this->assertEquals($expected, $p, 'add two sub-query results');
}
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 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 testIssue65()
{
$sql = "select i1, count(*) cnt from test.s1 group by i1";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue65.serialized');
$this->assertEquals($expected, $p, 'It treats the alias as a colref.');
}
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 testIssue122()
{
$sql = "UPDATE db.`tablename` SET a=1";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue122.serialized');
$this->assertEquals($expected, $p, 'no_quotes property has been corrected');
}
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 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 testGregory()
{
$query = "\n\t\tSELECT * FROM project_relation pr\n\t\tJOIN Project_type2 t2 ON FIND_IN_SET(id , pr.projet_type2) AND FIND_IN_SET('FR' , t2.pays) \n\t\t";
$p = $this->parser->parse($query, true);
$this->log($p);
$expected = getExpectedValue(dirname(__FILE__), 'gregory.serialized');
$this->assertEquals($expected, $p, 'position problems with multiple functions in joins');
}
public function testIssue139()
{
$sql = "SELECT x,y,z FROM tableA WHERE x<5 limit 2 offset 0";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue139.serialized');
$this->assertEquals($expected, $p, 'lowercase OFFSET should not fail');
}
public function testIssue148()
{
$sql = "SELECT REPLACE(NOW(), '-', '')";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue148.serialized');
$this->assertEquals($expected, $p, 'REPLACE prevents position calculation');
}
public function testIssueGit185()
{
$query = "\n\t\tSELECT seen, id, name, cep, date_format(created,'%d/%m/%Y %h:%i:%s') as created\n FROM user\n WHERE approved = 0 and canceled = 0\n\t\t";
$p = $this->parser->parse($query, true);
$created = $this->creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git185.sql', false);
$this->assertEquals($expected, $created, 'haha');
}
public function testIssue137()
{
$sql = "select`title`from`table`";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue137.serialized');
$this->assertEquals($expected, $p, 'SQL statements without whitespace');
}
public function testIssue136b()
{
$sql = "WITH myTableName AS (\n select firstname, lastname from employee where lastname = 'test'\n ), another_table AS (\n \t\tselect x,y FROM z\n \t\t)\n SELECT firstname FROM myTableName, another_table";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue136b.serialized');
$this->assertEquals($expected, $p, 'ORACLE\'s WITH statement');
}
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 testIssue149()
{
$sql = "SELECT * from tab where ifnull(col_name,'') <> ''";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue149.serialized');
$this->assertEquals($expected, $p, 'ifnull() doesn\'t parse properly');
}
public function testIssue69()
{
$sql = "select * from table1 where col1<>col2 or col3 is null";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue69.serialized');
$this->assertEquals($expected, $p, 'col is null should not fail.');
}
public function testIssue135()
{
$sql = "SELECT x,y,z FROM tableA WHERE x<5 GROUP BY STD(y)";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue135.serialized');
$this->assertEquals($expected, $p, 'STD must be an aggregate function');
}
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 testIssue61()
{
$sql = "SELECT lcase(dummy.b) FROM dummy ORDER BY dummy.a, LCASE(dummy.b)";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue61.serialized');
$this->assertEquals($expected, $p, 'functions/expressions within ORDER-BY');
}
public function testIssueGit181()
{
$query = "\n\t\tSELECT NOW() AS today\n\t\t";
$p = $this->parser->parse($query, true);
$created = $this->creator->create($p);
$expected = getExpectedValue(dirname(__FILE__), 'issue_git181.sql', false);
$this->assertEquals($expected, $created, 'no alias after functions without parameters');
}
public function testIssue93()
{
$sql = "select 1 as `a` order by `a`";
$parser = new PHPSQLParser($sql, true);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue93.serialized');
$this->assertEquals($expected, $p, 'simple query');
}
public function testIssue72()
{
$sql = "update table set col=@max";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue72.serialized');
$this->assertEquals($expected, $p, 'user defined variables should not fail');
}
public function testIssue138()
{
$sql = "SELECT ivoid, access_url, name, ucd, description FROM rr.capability NATURAL JOIN rr.interface NATURAL JOIN rr.table_column NATURAL JOIN rr.res_table WHERE standard_id='ivo://ivoa.net/std/TAP' AND 1=ivo_hasword(table_description, 'quasar') AND ucd='phot.mag;em.opt.V'";
$parser = new PHPSQLParser($sql);
$p = $parser->parsed;
$expected = getExpectedValue(dirname(__FILE__), 'issue138.serialized');
$this->assertEquals($expected, $p, 'NATURAL JOIN should not be an alias');
}