<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = "SELECT title, introtext\nFROM kj9un_content\nWHERE `id`='159'"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue43.serialized'); eq_array($p, $expected, 'problem with linefeed after tablename');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $parser = new PHPSQLParser(); $sql = "INSERT INTO test (`name`, `test`) VALUES ('\\'Superman\\'', ''), ('\\'Superman\\'', '')"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue36a.serialized'); eq_array($p, $expected, 'INSERT statement with escaped quotes and multiple records'); $sql = "INSERT INTO test (`name`, `test`) VALUES ('\\'Superman\\'', '')"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue36b.serialized'); eq_array($p, $expected, 'INSERT statement with escaped quotes and one record'); $sql = "INSERT INTO test (`name`, `test`) VALUES ('\\'Superman\\'', ''), ('\\'sdfsd\\'', '')"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue36c.serialized'); eq_array($p, $expected, 'INSERT statement with escaped quotes and multiple records (2)');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "select 1 as `a` order by `a`"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue93.serialized'); eq_array($p, $expected, 'simple query');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $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'); eq_array($p, $expected, 'very long statement');
<?php require_once dirname(__FILE__) . "/../../../php-sql-parser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $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'); eq_array($p, $expected, 'parenthesis problem on issue 25');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; # optimizer/index hints # not solved $parser = new PHPSQLParser(); $sql = "insert /* +APPEND */ into TableName (Col1,col2) values(1,'pol')"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue56a.serialized'); eq_array($p, $expected, 'optimizer hint within INSERT'); # inline comment # not solved $sql = "SELECT acol -- an inline comment\nFROM --another comment\ntable\nWHERE x = 1"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue56b.serialized'); eq_array($p, $expected, 'inline comment should not fail, issue 56');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "select i1, count(*) cnt from test.s1 group by i1"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue65.serialized'); eq_array($p, $expected, 'It treats the alias as a colref.');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = 'SELECT (select colA FRom TableA) as b From test t'; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'subselect1.serialized'); eq_array($p, $expected, 'sub-select with alias'); $sql = 'SELECT a.uid, a.users_name FROM USERS AS a LEFT JOIN (SELECT uid AS id FROM USER_IN_GROUPS WHERE ugid = 1) AS b ON a.uid = b.id WHERE id IS NULL ORDER BY a.users_name'; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'subselect2.serialized'); eq_array($p, $expected, 'sub-select as table replacement with alias');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $sql = "select * from table1 as event"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue71a.serialized'); eq_array($p, $expected, 'infinite loop on table alias "event"'); $sql = "select acol from table as data"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue71b.serialized'); eq_array($p, $expected, 'infinite loop on table alias "data"');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $parser = new PHPSQLParser(); $sql = "select a from t where x = \"a'b\\cd\" and y = 'ef\"gh'"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue40a.serialized'); eq_array($p, $expected, 'escaped characters 1'); $q2 = "select a from t where x = \"abcd\" and y = 'efgh'"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue40b.serialized'); eq_array($p, $expected, 'escaped characters 2');
<?php require_once dirname(__FILE__) . "/../../../php-sql-parser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = 'SELECT colA hello From test t'; $p = $parser->parse($sql, true); ok($p['SELECT'][0]['position'] == 7, 'position of column'); ok($p['SELECT'][0]['alias']['position'] == 12, 'position of column alias'); ok($p['FROM'][0]['position'] == 23, 'position of table'); ok($p['FROM'][0]['alias']['position'] == 28, 'position of table alias'); $sql = "SELECT colA hello From test\nt"; $p = $parser->parse($sql, true); ok($p['SELECT'][0]['position'] == 7, 'position of column'); ok($p['SELECT'][0]['alias']['position'] == 12, 'position of column alias'); ok($p['FROM'][0]['position'] == 23, 'position of table'); ok($p['FROM'][0]['alias']['position'] == 28, 'position of table alias'); $sql = "SELECT a.*, c.*, u.users_name FROM SURVEYS as a INNER JOIN SURVEYS_LANGUAGESETTINGS as c ON ( surveyls_survey_id = a.sid AND surveyls_language = a.language ) AND surveyls_survey_id=a.sid and surveyls_language=a.language INNER JOIN USERS as u ON (u.uid=a.owner_id) ORDER BY surveyls_title"; $p = $parser->parse($sql, true); $expected = getExpectedValue(dirname(__FILE__), 'positions1.serialized'); eq_array($p, $expected, 'a long query with join and order clauses');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "SELECT CAST((CONCAT(table1.col1,' ',time_start)) AS DATETIME) FROM table1"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue62.serialized'); eq_array($p, $expected, 'CAST expression');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = "SELECT COUNT(DISTINCT bla) FROM foo"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue39.serialized'); eq_array($p, $expected, 'count(distinct x)');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = "SELECT CAST( 12 AS decimal( 9, 3 ) )"; $parser->parse($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue51.serialized'); eq_array($p, $expected, 'should not die if query contains cast expression');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = "drop table if exists xyz cascade"; $p = $parser->parse($sql, true); $expected = getExpectedValue(dirname(__FILE__), 'drop.serialized'); eq_array($p, $expected, 'drop table statement');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $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'); eq_array($p, $expected, 'escaped quote in string constant');
require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = 'SELECT 1'; $p = $parser->parse($sql); ok(count($p) == 1 && count($p['SELECT']) == 1); ok($p['SELECT'][0]['expr_type'] == 'const'); ok($p['SELECT'][0]['base_expr'] == '1'); ok($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; ok(count($p) == 3 && count($p['SELECT']) == 8); ok($p['SELECT'][count($p['SELECT']) - 1]['base_expr'] == 'terminate'); ok(count($p) == 3 && count($p['FROM']) == 1); ok(count($p) == 3 && count($p['WHERE']) == 3); $parser->parse('SELECT NOW( ),now(),sysdate( ),sysdate () as now'); ok($parser->parsed['SELECT'][3]['base_expr'] == 'sysdate'); $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'); eq_array($p, $expected, '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'); eq_array($p, $expected, 'select for update');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $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'); eq_array($p, $expected, 'functions/expressions within ORDER-BY');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $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'); eq_array($p, $expected, 'col is null should not fail.');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "select `column` from table where col=\"value\""; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue70.serialized'); eq_array($p, $expected, 'quotes after an operator should not fail.');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = 'SELECT SUM( 10 ) as test FROM account'; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized'); eq_array($p, $expected, 'only space characters within SQL statement'); $sql = "SELECT\tSUM( 10 ) \tas test FROM account"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'issue21.serialized'); // should be the same as above eq_array($p, $expected, 'tab character within SQL statement');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; // thats an issue written as comment into the ParserManual... // TODO: the ON clause base_expr contains ")", which fails in PositionCalculator->findPositionWithinString() $sql = "SELECT FROM some_table a LEFT JOIN another_table AS b ON FIND_IN_SET(a.id, b.ids_collection)"; try { $parser = new PHPSQLParser($sql, true); } catch (UnableToCalculatePositionException $e) { echo $e->getMessage(); } $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'manual.serialized'); eq_array($p, $expected, 'no select expression');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $parser = new PHPSQLParser(); $sql = "SELECT * FROM v\$mytable"; $parser->parse($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue41.serialized'); eq_array($p, $expected, 'escaped $ in tablename');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "show columns from `foo.bar`"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'show1.serialized'); eq_array($p, $expected, 'show columns from'); $sql = "show CREATE DATABASE `foo`"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'show2.serialized'); eq_array($p, $expected, 'show create database'); $sql = "show DATABASES LIKE '%bar%'"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'show3.serialized'); eq_array($p, $expected, 'show databases like'); $sql = "SHOW ENGINE foo STATUS"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'show4.serialized'); eq_array($p, $expected, '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'); eq_array($p, $expected, 'show full columns from like');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = 'SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH) AS next_month'; $parser = new PHPSQLParser(); $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'issue94.serialized'); eq_array($p, $expected, 'date_add()');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; // TODO: the SET statement doesn't work completely, SESSION is not a colref! $sql = "SET SESSION group_concat_max_len = @@max_allowed_packet"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue67a.serialized'); eq_array($p, $expected, '@ character after operator should not fail.'); // this is ok $sql = "SET @a = 1"; $parser = new PHPSQLParser($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue67b.serialized'); eq_array($p, $expected, 'user defined variables should not fail');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $sql = "select webid, floor(iz/2.) as `fl` from MDR1.Tweb512 as `w` where w.webid < 100"; $parser = new PHPSQLParser($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue98.serialized'); eq_array($p, $expected, 'alias with quotes');
<?php require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = 'SELECT a.field1, b.field1, c.field1 FROM tablea a LEFT JOIN tableb b ON b.ida = a.id LEFT JOIN tablec c ON c.idb = b.id;'; $parser->parse($sql, true); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'left1.serialized'); eq_array($p, $expected, 'left join with alias'); $sql = 'SELECT a.field1, b.field1, c.field1 FROM tablea a LEFT OUTER JOIN tableb b ON b.ida = a.id RIGHT JOIN tablec c ON c.idb = b.id JOIN tabled d USING (d_id) right outer join e on e.id = a.e_id; left join e e2 using (e_id) join e e3 on (e3.e_id = e2.e_id)'; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'left2.serialized'); eq_array($p, $expected, 'right and left outer joins');
<?php require_once dirname(__FILE__) . '/../../../php-sql-parser.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $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\nFROM\tstats AS st,\n\t\tsponsor AS sp\nWHERE\tst.id=sp.id\nGROUP BY st.id\nORDER BY sp.alt_en asc, sp.alt_pl asc"; $parser->parse($sql); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue31.serialized'); eq_array($p, $expected, 'very complex statement with keyword view as alias');
<?php require_once dirname(__FILE__) . "/../../../php-sql-parser.php"; require_once dirname(__FILE__) . "/../../test-more.php"; $parser = new PHPSQLParser(); $sql = "SELECT * FROM FAILED_LOGIN_ATTEMPTS WHERE ip='192.168.50.5'"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'allcolumns1.serialized'); eq_array($p, $expected, 'single all column alias'); $sql = "SELECT a * b FROM tests"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'allcolumns2.serialized'); eq_array($p, $expected, 'multiply two columns'); $sql = "SELECT count(*) FROM tests"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'allcolumns3.serialized'); eq_array($p, $expected, 'special function count(*)'); $sql = "SELECT a.* FROM FAILED_LOGIN_ATTEMPTS a"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'allcolumns4.serialized'); eq_array($p, $expected, 'single all column alias with table alias'); $sql = "SELECT a, * FROM tests"; $p = $parser->parse($sql); $expected = getExpectedValue(dirname(__FILE__), 'allcolumns5.serialized'); eq_array($p, $expected, 'column reference and a single all column alias');