public static function query($query, $params = array())
{
$default_comparator = "binary";
if (preg_match("/^\\s*SHOW TABLES\$/i", $query)) {
return MMB::showTables();
}
$parser = new \PHPSQLParser();
$parsed = $parser->parse($query);
$parsed["SQL"] = $query;
self::debug(print_r($parsed, true));
if (isset($parsed["SELECT"])) {
return self::processSelectStatement($parsed);
} else {
if (isset($parsed["INSERT"], $parsed["VALUES"])) {
return self::processInsertStatement($parsed);
} else {
if (isset($parsed["UPDATE"])) {
print_r($parsed);
return self::processUpdateStatement($parsed);
} else {
if (isset($parsed["DELETE"])) {
return self::processDeleteStatement($parsed);
}
}
}
}
if ($params["link_identifier"]) {
return \mysql_query($query, $params["link_identifier"]);
}
return array("1");
}
public function testQueryIsParsedWithoutErrors()
{
$sql = '(SELECT * FROM users) ORDER BY id';
$parser = new PHPSQLParser();
$result = $parser->parse($sql);
$this->assertInternalType('array', $result);
}
/**
* @param string $query
*/
public function __construct($query)
{
$parser = new \PHPSQLParser();
$this->parsedQuery = $parser->parse($query);
$this->rawQuery = $query;
$this->hash = sha1(serialize($this->parsedQuery));
}
public function testShouldHandleJoinableQueriesCorrectly()
{
$query = 'SELECT
c.id,
c.isin,
c.currency,
c.issuer,
c.issuer_price,
h.current_price,
c.trading_market,
bc.barrier_level,
gc.participation_rate,
c.type
FROM
history h
LEFT JOIN certificate c ON (h.certificate_id = c.id)
INNER JOIN history h2 ON (h.certificate_id = h2.certificate_id)
LEFT JOIN bonus_certificate bc ON (c.id = bc.certificate_id)
LEFT JOIN guarantee_certificate gc ON (c.id = gc.certificate_id)
WHERE
h.created = h2.created
AND
c.type = 2
GROUP BY c.id';
$parser = new \PHPSQLParser();
$adapter = new \Nfx\SqlQueryBuilder\Adapter\ZendFrameworkAdapter();
$adapter->setParsedQuery($parser->parse($query));
$builder = $adapter->getBuilder();
$expected = '...';
$this->assertInstanceOf('Zend\\Db\\Sql\\Select', $builder);
$this->assertEquals($expected, $builder->getSqlString());
}
public function testQueryHashing()
{
$sql = "SELECT foo FROM bar WHERE baz";
$q = new \Pseudo\ParsedQuery($sql);
$p = new \PHPSQLParser();
$parsed = $p->parse($sql);
$hashed = sha1(serialize($parsed));
$this->assertEquals($hashed, $q->getHash());
}
public function testEmptyProcessSelectExpr()
{
$parser = new PHPSQLParser();
//the key part of the sql string is the parenthesis. This use case is more prone to fail in mssql environments
//this will eventually result in PHPSQLParser::process_select_expr() processing an empty string, which is the
//error we are testing for.
$sql = '(SELECT contacts.* FROM contacts ) ORDER BY contacts.phone_work asc';
$sqlARR = $parser->parse($sql);
// assert an array was returned. If an error occurred a php fatal error will bubble up and nothing will be returned
$this->assertInternalType('array', $sqlARR, "there was an error while parsing the sql string: " . $sql);
}
/**
* Parse SQL query WHERE and ORDER BY clauses and validate that nothing bad is happening there
* @param string $where
* @param string $order_by
* @return bool
*/
public function validateQueryClauses($where, $order_by = '')
{
if (empty($where) && empty($order_by)) {
return true;
}
if (empty($where) && !empty($order_by)) {
$where = "deleted=0";
}
$parser = new PHPSQLParser();
$testquery = "SELECT dummy FROM dummytable WHERE {$where}";
$clauses = 3;
if (!empty($order_by)) {
$testquery .= " ORDER BY {$order_by}";
$clauses++;
}
$parsed = $parser->parse($testquery);
//$GLOBALS['log']->debug("PARSE: ".var_export($parsed, true));
if (count($parsed) != $clauses) {
// we assume: SELECT, FROM, WHERE, maybe ORDER
return false;
}
$parts = array_keys($parsed);
if ($parts[0] != "SELECT" || $parts[1] != "FROM" || $parts[2] != "WHERE") {
// check the keys to be SELECT, FROM, WHERE
return false;
}
if (!empty($order_by) && $parts[3] != "ORDER") {
// extra key is ORDER
return false;
}
// verify SELECT didn't change
if (count($parsed["SELECT"]) != 1 || $parsed["SELECT"][0] !== array('expr_type' => 'colref', 'alias' => '`dummy`', 'base_expr' => 'dummy', 'sub_tree' => false)) {
$GLOBALS['log']->debug("validation failed SELECT");
return false;
}
// verify FROM didn't change
if (count($parsed["FROM"]) != 1 || $parsed["FROM"][0] !== array('table' => 'dummytable', 'alias' => 'dummytable', 'join_type' => 'JOIN', 'ref_type' => '', 'ref_clause' => '', 'base_expr' => false, 'sub_tree' => false)) {
$GLOBALS['log']->debug("validation failed FROM");
return false;
}
// check WHERE
if (!$this->validateExpression($parsed["WHERE"], true)) {
$GLOBALS['log']->debug("validation failed WHERE");
return false;
}
// check ORDER
if (!empty($order_by) && !$this->validateExpression($parsed["ORDER"])) {
$GLOBALS['log']->debug("validation failed ORDER");
return false;
}
return true;
}
<?php
require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
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;
<?php require_once dirname(__FILE__) . '/../../../src/PHPSQLParser.php'; require_once dirname(__FILE__) . '/../../../src/PHPSQLCreator.php'; require_once dirname(__FILE__) . '/../../test-more.php'; $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); ok($created === $expected, 'unary operator');
<?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"; // TODO: not solved, charsets are not possible at the moment $parser = new PHPSQLParser(); $sql = "SELECT _utf8'hi'"; $parser->parse($sql, false); $p = $parser->parsed; $expected = getExpectedValue(dirname(__FILE__), 'issue50.serialized'); eq_array($p, $expected, 'does not die if query contains _utf8'); $sql = "SELECT _utf8'hi' COLLATE latin1_german1_ci"; $parser->parse($sql, false); $p = $parser->parsed; # hex value $sql = "SELECT _utf8 x'AABBCC'"; $sql = "SELECT _utf8 0xAABBCC"; # binary value $sql = "SELECT _utf8 b'0001'"; $sql = "SELECT _utf8 0b0001";
<?php
require_once dirname(__FILE__) . "/../../../src/PHPSQLParser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
try {
$sql = "SELECT PERCENTILE(xyz, 90) as percentile from some_table";
$parser = new PHPSQLParser();
$parser->addCustomFunction("percentile");
$p = $parser->parse($sql, true);
} catch (Exception $e) {
$p = array();
}
ok($p['SELECT'][0]['expr_type'] === ExpressionType::CUSTOM_FUNCTION, 'custom function within SELECT clause');
#!/usr/bin/env php
<?php
/**
* you cannot execute this script within Eclipse PHP
* because of the limited output buffer. Try to run it
* directly within a shell.
*/
require_once dirname(__FILE__) . '/../src/PHPSQLParser.php';
$parser = new PHPSQLParser();
// var_dump($parser);
// var_dump($argv);
// echo "Argsize =\n";
// echo sizeof($argv) . "\n";
if (sizeof($argv) < 2) {
// echo "Not enough arguments...\n";
// echo "Checking if url parameter";
$sql = $_GET['kqlq'];
echo json_encode($parser->parse($sql, true));
// echo "First argument has to be KQL query\n";
// exit(1);
} else {
$sql = $argv[1];
// echo json_encode($parser->parse($sql, true));
print_r(json_encode($parser->parse($sql, true)));
}
/* You can use the ->parse() method too. The parsed structure is returned, and
also available in the ->parsed property. */
// $sql = 'SELECT \'a,b,c\'
// from \'some_table an_alias\'
// where \'d > 5;\'';
// $sql = $argv[1];
function GetAggregates($_arr)
{
$_parser = new PHPSQLParser();
$_parser->parse($this->SelectCommand);
$_parsed_query = $_parser->parsed;
//Filter
$_filters = $this->Filters;
if (count($_filters) > 0) {
for ($i = 0; $i < sizeof($_filters); $i++) {
if (!isset($_parsed_query["WHERE"])) {
$_parsed_query["WHERE"] = array();
} else {
array_push($_parsed_query["WHERE"], array("expr_type" => "operator", "base_expr" => "and", "sub_tree" => false));
}
$_epx = $this->GetFilterExpression($_filters[$i]);
array_push($_parsed_query["WHERE"], array("expr_type" => "colref", "base_expr" => $_epx["field"], "sub_tree" => false));
array_push($_parsed_query["WHERE"], array("expr_type" => "operator", "base_expr" => $_epx["expression"], "sub_tree" => false));
array_push($_parsed_query["WHERE"], array("expr_type" => "const", "base_expr" => $_epx["value"], "sub_tree" => false));
}
}
//Sort
$_sorts = $this->Sorts;
if (count($_sorts) > 0) {
for ($i = 0; $i < sizeof($_sorts); $i++) {
if (!isset($_parsed_query["ORDER"])) {
$_parsed_query["ORDER"] = array();
}
array_push($_parsed_query["ORDER"], array("expr_type" => "colref", "base_expr" => $_sorts[$i]->Field, "sub_tree" => false, "direction" => $_sorts[$i]->Order));
}
}
//Group
$_groups = $this->Groups;
if (count($_groups) > 0) {
for ($i = 0; $i < sizeof($_groups); $i++) {
if (!isset($_parsed_query["GROUP"])) {
$_parsed_query["GROUP"] = array();
}
array_push($_parsed_query["GROUP"], array("expr_type" => "colref", "base_expr" => $_groups[$i]->Field, "sub_tree" => false));
}
}
//SELECT
$_parsed_query["SELECT"] = array(array("expr_type" => "colref", "base_expr" => "{arggregate}", "sub_tree" => false, "alias" => false));
$_text = "";
foreach ($_arr as $_aggregate) {
if (strpos("-|min|max|first|last|count|sum|avg|", "|" . strtolower($_aggregate["Aggregate"]) . "|") > 0) {
$_text .= ", " . $_aggregate["Aggregate"] . "(" . $_aggregate["DataField"] . ") as " . $_aggregate["Key"];
}
}
$_agg_result = array();
if ($_text != "") {
$_text = substr($_text, 2);
$_creator = new PHPSQLCreator($_parsed_query);
$_select_command = $_creator->created;
$_select_command = str_replace("{arggregate}", $_text, $_select_command);
$_result = mysql_query($_select_command, $this->_Link);
$_agg_result = mysql_fetch_assoc($_result);
mysql_free_result($_result);
//-----
}
return $_agg_result;
}
/**
* FIXME: verify and thoroughly test this code, these regexps look fishy
* @see DBManager::limitQuery()
*/
public function limitQuery($sql, $start, $count, $dieOnError = false, $msg = '', $execute = true)
{
$start = (int) $start;
$count = (int) $count;
$newSQL = $sql;
$distinctSQLARRAY = array();
if (strpos($sql, "UNION") && !preg_match("/(')(UNION).?(')/i", $sql)) {
$newSQL = $this->handleUnionLimitQuery($sql, $start, $count);
} else {
if ($start < 0) {
$start = 0;
}
$GLOBALS['log']->debug(print_r(func_get_args(), true));
$this->lastsql = $sql;
$matches = array();
preg_match('/^(.*SELECT\\b)(.*?\\bFROM\\b.*\\bWHERE\\b)(.*)$/isU', $sql, $matches);
if (!empty($matches[3])) {
if ($start == 0) {
$match_two = strtolower($matches[2]);
if (!strpos($match_two, "distinct") > 0 && strpos($match_two, "distinct") !== 0) {
$orderByMatch = array();
preg_match('/^(.*)(\\bORDER BY\\b)(.*)$/is', $matches[3], $orderByMatch);
if (!empty($orderByMatch[3])) {
$selectPart = array();
preg_match('/^(.*)(\\bFROM\\b.*)$/isU', $matches[2], $selectPart);
$newSQL = "SELECT TOP {$count} * FROM\n (\n " . $matches[1] . $selectPart[1] . ", ROW_NUMBER()\n OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number\n " . $selectPart[2] . $orderByMatch[1] . "\n ) AS a\n WHERE row_number > {$start}";
} else {
$newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
}
} else {
$distinct_o = strpos($match_two, "distinct");
$up_to_distinct_str = substr($match_two, 0, $distinct_o);
//check to see if the distinct is within a function, if so, then proceed as normal
if (strpos($up_to_distinct_str, "(")) {
//proceed as normal
$newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
} else {
//if distinct is not within a function, then parse
//string contains distinct clause, "TOP needs to come after Distinct"
//get position of distinct
$match_zero = strtolower($matches[0]);
$distinct_pos = strpos($match_zero, "distinct");
//get position of where
$where_pos = strpos($match_zero, "where");
//parse through string
$beg = substr($matches[0], 0, $distinct_pos + 9);
$mid = substr($matches[0], strlen($beg), $where_pos + 5 - strlen($beg));
$end = substr($matches[0], strlen($beg) + strlen($mid));
//repopulate matches array
$matches[1] = $beg;
$matches[2] = $mid;
$matches[3] = $end;
$newSQL = $matches[1] . " TOP {$count} " . $matches[2] . $matches[3];
}
}
} else {
$orderByMatch = array();
preg_match('/^(.*)(\\bORDER BY\\b)(.*)$/is', $matches[3], $orderByMatch);
//if there is a distinct clause, parse sql string as we will have to insert the rownumber
//for paging, AFTER the distinct clause
$grpByStr = '';
$hasDistinct = strpos(strtolower($matches[0]), "distinct");
require_once 'include/php-sql-parser.php';
$parser = new PHPSQLParser();
$sqlArray = $parser->parse($sql);
if ($hasDistinct) {
$matches_sql = strtolower($matches[0]);
//remove reference to distinct and select keywords, as we will use a group by instead
//we need to use group by because we are introducing rownumber column which would make every row unique
//take out the select and distinct from string so we can reuse in group by
$dist_str = 'distinct';
preg_match('/\\b' . $dist_str . '\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$matches_sql = trim(substr($matches_sql, $matchesPartSQL[0][1] + strlen($dist_str)));
//get the position of where and from for further processing
preg_match('/\\bfrom\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$from_pos = $matchesPartSQL[0][1];
preg_match('/\\where\\b/simU', $matches_sql, $matchesPartSQL, PREG_OFFSET_CAPTURE);
$where_pos = $matchesPartSQL[0][1];
//split the sql into a string before and after the from clause
//we will use the columns being selected to construct the group by clause
if ($from_pos > 0) {
$distinctSQLARRAY[0] = substr($matches_sql, 0, $from_pos);
$distinctSQLARRAY[1] = substr($matches_sql, $from_pos);
//get position of order by (if it exists) so we can strip it from the string
$ob_pos = strpos($distinctSQLARRAY[1], "order by");
if ($ob_pos) {
$distinctSQLARRAY[1] = substr($distinctSQLARRAY[1], 0, $ob_pos);
}
// strip off last closing parentheses from the where clause
$distinctSQLARRAY[1] = preg_replace('/\\)\\s$/', ' ', $distinctSQLARRAY[1]);
}
$grpByStr = array();
foreach ($sqlArray['SELECT'] as $record) {
if ($record['expr_type'] == 'const') {
continue;
}
$grpByStr[] = trim($record['base_expr']);
}
$grpByStr = implode(', ', $grpByStr);
}
if (!empty($orderByMatch[3])) {
//if there is a distinct clause, form query with rownumber after distinct
if ($hasDistinct) {
$newSQL = "SELECT TOP {$count} * FROM\n (\n SELECT ROW_NUMBER()\n OVER (ORDER BY " . preg_replace('/^' . $dist_str . '\\s+/', '', $this->returnOrderBy($sql, $orderByMatch[3])) . ") AS row_number,\n count(*) counter, " . $distinctSQLARRAY[0] . "\n " . $distinctSQLARRAY[1] . "\n group by " . $grpByStr . "\n ) AS a\n WHERE row_number > {$start}";
} else {
$newSQL = "SELECT TOP {$count} * FROM\n (\n " . $matches[1] . " ROW_NUMBER()\n OVER (ORDER BY " . $this->returnOrderBy($sql, $orderByMatch[3]) . ") AS row_number,\n " . $matches[2] . $orderByMatch[1] . "\n ) AS a\n WHERE row_number > {$start}";
}
} else {
//if there is a distinct clause, form query with rownumber after distinct
if ($hasDistinct) {
$newSQL = "SELECT TOP {$count} * FROM\n (\n SELECT ROW_NUMBER() OVER (ORDER BY " . $grpByStr . ") AS row_number, count(*) counter, " . $distinctSQLARRAY[0] . "\n " . $distinctSQLARRAY[1] . "\n group by " . $grpByStr . "\n )\n AS a\n WHERE row_number > {$start}";
} else {
$newSQL = "SELECT TOP {$count} * FROM\n (\n " . $matches[1] . " ROW_NUMBER() OVER (ORDER BY " . $sqlArray['FROM'][0]['alias'] . ".id) AS row_number, " . $matches[2] . $matches[3] . "\n )\n AS a\n WHERE row_number > {$start}";
}
}
}
}
}
$GLOBALS['log']->debug('Limit Query: ' . $newSQL);
if ($execute) {
$result = $this->query($newSQL, $dieOnError, $msg);
$this->dump_slow_queries($newSQL);
return $result;
} else {
return $newSQL;
}
}
<?php require 'php-sql-parser.php'; $parser = new \PHPSQLParser(); $query = "\nINSERT INTO test (\n`c1` ,\n`c2`\n)\nVALUES (\n1, 11\n), (\n2 , 22\n);\n"; $parsed = $parser->parse($query); $parsed["SQL"] = $query; print_r($parsed); echo microtime();
* you cannot execute this script within Eclipse PHP
* because of the limited output buffer. Try to run it
* directly within a shell.
*/
require_once 'php-sql-parser.php';
$sql = 'SELECT 1';
echo $sql . "\n";
$start = microtime(true);
$parser = new PHPSQLParser($sql, true);
print_r($parser->parsed);
echo "parse time simplest query:" . (microtime(true) - $start) . "\n";
/*You can use the constuctor for parsing. The parsed statement is stored at the ->parsed property.*/
$sql = 'REPLACE INTO table (a,b,c) VALUES (1,2,3)';
echo $sql . "\n";
$start = microtime(true);
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);
echo "parse time very somewhat simple statement:" . (microtime(true) - $start) . "\n";
/* You can use the ->parse() method too. The parsed structure is returned, and
also available in the ->parsed property. */
$sql = 'SELECT a,b,c
from some_table an_alias
where d > 5;';
echo $sql . "\n";
print_r($parser->parse($sql, true));
$sql = 'SELECT a,b,c
from some_table an_alias
join `another` as `another table` using(id)
where d > 5;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql, true);
<?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__) . "/../../../php-sql-parser.php";
require_once dirname(__FILE__) . "/../../test-more.php";
$parser = new PHPSQLParser();
$sql = "insert into SETTINGS_GLOBAL (stg_value,stg_name) values('','force_ssl')";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'insert1.serialized');
eq_array($p, $expected, 'insert some data into table');
$sql = " INSERT INTO settings_global VALUES ('DBVersion', '146')";
$p = $parser->parse($sql, true);
$expected = getExpectedValue(dirname(__FILE__), 'insert2.serialized');
eq_array($p, $expected, 'insert some data into table with positions');
$sql = "INSERT INTO surveys ( SID, OWNER_ID, ADMIN, ACTIVE, EXPIRES, STARTDATE, ADMINEMAIL, ANONYMIZED, FAXTO, FORMAT, SAVETIMINGS, TEMPLATE, LANGUAGE, DATESTAMP, USECOOKIE, ALLOWREGISTER, ALLOWSAVE, AUTOREDIRECT, ALLOWPREV, PRINTANSWERS, IPADDR, REFURL, DATECREATED, PUBLICSTATISTICS, PUBLICGRAPHS, LISTPUBLIC, HTMLEMAIL, TOKENANSWERSPERSISTENCE, ASSESSMENTS, USECAPTCHA, BOUNCE_EMAIL, EMAILRESPONSETO, EMAILNOTIFICATIONTO, TOKENLENGTH, SHOWXQUESTIONS, SHOWGROUPINFO, SHOWNOANSWER, SHOWQNUMCODE, SHOWWELCOME, SHOWPROGRESS, ALLOWJUMPS, NAVIGATIONDELAY, NOKEYBOARD, ALLOWEDITAFTERCOMPLETION ) \nVALUES ( 32225, 1, 'André', 'N', null, null, '*****@*****.**', 'N', '', 'G', 'N', 'default', 'de-informal', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', a_function('2012-02-16','YYYY-MM-DD'), 'N', 'N', 'Y', 'Y', 'N', 'N', 'D', '*****@*****.**', '', '', 15, 'Y', 'B', 'Y', 'X', 'Y', 'Y', 'N', 0, 'N', 'N' )";
$p = $parser->parse($sql);
$expected = getExpectedValue(dirname(__FILE__), 'insert3.serialized');
eq_array($p, $expected, 'insert with user-function');
<?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');
/**
* Load fixtures into database from fixtures file
*/
public function actionLoad($truncateMode = false)
{
$startTime = microtime(true);
// check time of start execution script
$startMemory = round(memory_get_usage(true) / 1048576, 2);
echo "[36m Are you sure you want to load fixtures? Your database will be purged! [Y/N] [0m";
$handle = fopen("php://stdin", "r");
$line = fgets($handle);
$purgedLine = preg_replace('/[^A-Za-z0-9\\-]/', '', $line);
// or trim($line)
if (strtolower($purgedLine) == 'n') {
echo "[34m Stopping the executing... Done. [0m \n";
die;
}
// assign file variable what consist full path to fixtures file
$file = empty($this->pathToFixtures) ? __DIR__ . '/fixtures.php' : $this->pathToFixtures;
// import models classes to make available create new instances
if (empty($this->modelsFolder)) {
// if property empty default load form the models folder in protected directory
Yii::import('application.models.*');
} else {
if (is_array($this->modelsFolder)) {
// if modelsFolder defined check if it array of models
foreach ($this->modelsFolder as $key => $folder) {
Yii::import($folder);
//import each folder form array
}
} else {
Yii::import($this->modelsFolder);
//import single models folder
}
}
if (!file_exists($file)) {
// check if exist file with fixtures
echo "[33m There is no file with fixtures to load! Make sure that you create file with fixtures,\n or pass correct file name [0m \n";
die;
}
$fixtures = (require_once $file);
// require that file with fixtures, will be array
$errorList = array();
// create array what will consist model errors
if ($truncateMode) {
// if truncated mode ON
echo "[34m Wait database truncating... [0m \n\n";
require_once $this->php_sql_parser;
// require sql parser
$keys = array();
// prepare array for store FK`s data
foreach (Yii::app()->db->schema->getTables() as $tableSchema) {
// Run over all tables
$parser = new PHPSQLParser();
$result = Yii::app()->db->createCommand('SHOW CREATE TABLE ' . $tableSchema->name)->queryRow();
// getting create sql statement for current table
$parsed = $parser->parse($result['Create Table']);
//parse sql create statement
$table = $parsed['TABLE']['base_expr'];
// get table name
foreach ($parsed['TABLE']['create-def']['sub_tree'] as $key => $column_def) {
// run over all columns of the table
if ($column_def['expr_type'] != 'foreign-key') {
// check if column is FOREIGN KEY
continue;
}
$fkName = $column_def['sub_tree'][0]['sub_tree']['base_expr'];
// get FOREIGN KEY name
Yii::app()->db->createCommand()->dropForeignKey(str_replace('`', '', $fkName), str_replace('`', '', $table));
// frop FOREIGN KEY
$keys[] = array('name' => str_replace('`', '', $column_def['sub_tree'][0]['sub_tree']['base_expr']), 'table' => str_replace('`', '', $table), 'column' => $column_def['sub_tree'][3]['sub_tree'][0]['no_quotes'], 'ref_table' => $column_def['sub_tree'][4]['sub_tree'][1]['no_quotes'], 'ref_column' => $column_def['sub_tree'][4]['sub_tree'][2]['sub_tree'][0]['no_quotes'], 'update' => $column_def['sub_tree'][4]['sub_tree'][5]['base_expr'], 'delete' => $column_def['sub_tree'][4]['sub_tree'][5]['base_expr']);
}
}
foreach (Yii::app()->db->schema->getTables() as $tableSchema) {
// after drop all FK`s truncate each table
Yii::app()->db->createCommand()->truncateTable(str_replace('`', '', $tableSchema->name));
}
foreach ($keys as $identifier => $foreignKey) {
// recreate all FK`s to make sure schema is safe
Yii::app()->db->createCommand()->addForeignKey($foreignKey['name'], $foreignKey['table'], $foreignKey['column'], $foreignKey['ref_table'], $foreignKey['ref_column'], $foreignKey['update'], $foreignKey['delete']);
}
}
foreach ($fixtures as $modelClass => $instances) {
// run through the array with fixtures
$modelClass::model()->deleteAll();
// removing old rows from database if database is not truncated
foreach ($instances as $key => $instance) {
// go through all instances for certain model, and save it into db
$model = new $modelClass();
$model->attributes = $instances[$key];
if (!$model->save()) {
// if model can't be saved append errors into error list array
$errorList[] = $model->getErrors();
}
}
}
if (!empty($errorList)) {
// if error list not empty
echo "[31m Validation errors occurs during loading the fixtures, some fixtures wasn't loaded to database [0m \n\n [33m The next errors occur [0m \n";
foreach ($errorList as $key => $errors) {
// run over all errors and display error what occur during saving into db
foreach ($errors as $k => $error) {
foreach ($error as $i => $value) {
echo "[37;41m" . $value . "[0m \n";
//display error
}
}
}
die;
}
echo "[37;42m All fixtures loaded properly [0m \n\n Script execution time: " . round(microtime(true) - $startTime, 2) . " ms. \n";
$endMemory = round(memory_get_usage(true) / 1048576, 2);
echo "Memory used: " . ($endMemory - $startMemory) . "mb \n";
}
