Author: Justin Swanhart (greenlion@gmail.com)
Author: André Rothe (arothe@phosco.info)
 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()');
 }
Exemple #12
0
 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');
 }
Exemple #16
0
 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');
 }