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 testQueryIsParsedWithoutErrors() { $sql = '(SELECT * FROM users) ORDER BY id'; $parser = new PHPSQLParser(); $result = $parser->parse($sql); $this->assertInternalType('array', $result); }
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"); }
/** * @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 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; }
/** * 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_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');
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); print_r($parser->parsed); $sql = 'SELECT a,b,c from some_table an_alias join (select d, max(f) max_f from some_table where id = 37 group by d) `subqry` on subqry.d = an_alias.d where d > 5;'; echo $sql . "\n";
<?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();
<?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";
/** * 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]; //echo "Printing output\n" . $sql . "\n"; //print_r($parser->parse($sql, true));
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; }
<?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__) . '/../../../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"; }