public static function parseSQL($sql)
{
$tag = "SQLSelectQuery::parseSQL()";
Log::debug("{$tag}");
// Trim empty characters
$sql = trim($sql);
// Instantiate a new object
$query = new SQLSelectQuery();
// parse sql string to init data
$STATE_START = 0;
$STATE_SELECT = 1;
$STATE_FROM = 2;
$STATE_WHERE = 3;
$STATE_GROUP_BY = 4;
$STATE_HAVING = 5;
$STATE_ORDER_BY = 6;
$STATE_LIMIT = 7;
$state = $STATE_START;
$count_open_paren = 0;
$bool_open_single_quote = false;
$bool_open_double_quote = false;
$buff = "";
$strlen = strlen($sql);
for ($i = 0; $i < strlen($sql); $i++) {
$char = $sql[$i];
// keep track of open parenthesis, single, and double quotes
if ("(" == $char) {
$count_open_paren++;
} else {
if (")" == $char) {
$count_open_paren--;
} else {
if ("'" == $char) {
$bool_open_single_quote = !$bool_open_single_quote;
} else {
if ("\"" == $char) {
$bool_open_double_quote = !$bool_open_double_quote;
}
}
}
}
switch ($state) {
case $STATE_START:
// wait until state changes to STATE_SELECT
if ("SELECT" == strtoupper($buff)) {
// change state
$state = $STATE_SELECT;
// clear buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
throw new Exception("{$tag}: Syntax Error: Missing required SELECT clause");
} else {
// append current character to the sequence buffer
$buff .= $char;
}
}
break;
case $STATE_SELECT:
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) {
// add sequence to SELECT cluase
$query->select($buff);
// clear sequence buffer
$buff = "";
} else {
if ("FROM" == strtoupper(substr($buff, strlen("FROM") - strlen($buff)))) {
// add sequence (not including FROM) to SELECT clause
$query->select(substr($buff, 0, strpos(strtoupper($buff), "FROM")));
// change state
$state = $STATE_FROM;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to SELECT clause
$query->select($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
break;
case $STATE_FROM:
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) {
// add sequence to FROM cluase
$query->from($buff);
// clear sequence buffer
$buff = "";
} else {
if ("WHERE" == strtoupper(substr($buff, strlen("WHERE") - strlen($buff)))) {
// add sequence (not including WHERE) to FROM clause
$query->from(substr($buff, 0, strpos(strtoupper($buff), "WHERE")));
// change state
$state = $STATE_WHERE;
// clear sequence buffer
$buff = "";
} else {
if ("GROUP BY" == strtoupper(substr($buff, strlen("GROUP BY") - strlen($buff)))) {
// add sequence (not including GROUP BY) to FROM clause
$query->from(substr($buff, 0, strpos(strtoupper($buff), "GROUP BY")));
// change state
$state = $STATE_GROUP_BY;
// clear sequence buffer
$buff = "";
} else {
if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) {
// add sequence (not including ORDER BY) to FROM clause
$query->select(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY")));
// change state
$state = $STATE_ORDER_BY;
// clear sequence buffer
$buff = "";
} else {
if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) {
// add sequence (not including LIMIT) to FROM clause
$query->select(substr($buff, 0, strpos(strtoupper($buff), "LIMIT")));
// change state
$state = $STATE_LIMIT;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to FROM clause
$query->from($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
}
}
}
break;
case $STATE_WHERE:
// Include entire WHERE clause from sql string to parse as a single element
// this simplifies the inclusion of logical operators in the sql string
// Subsequent WHERE clauses added with where() will be added to the query with AND operator
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ("GROUP BY" == strtoupper(substr($buff, strlen("GROUP BY") - strlen($buff)))) {
// add sequence (not including GROUP BY) to WHERE clause
$query->where(substr($buff, 0, strpos(strtoupper($buff), "GROUP BY")));
// change state
$state = $STATE_GROUP_BY;
// clear sequence buffer
$buff = "";
} else {
if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) {
// add sequence (not including ORDER BY) to WHERE clause
$query->where(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY")));
// change state
$state = $STATE_ORDER_BY;
// clear sequence buffer
$buff = "";
} else {
if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) {
// add sequence (not including LIMIT) to WHERE clause
$query->where(substr($buff, 0, strpos(strtoupper($buff), "LIMIT")));
// change state
$state = $STATE_LIMIT;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to WHERE clause
$query->where($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
}
break;
case $STATE_GROUP_BY:
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) {
// add sequence to GROUP BY cluase
$query->groupBy($buff);
// clear sequence buffer
$buff = "";
} else {
if ("HAVING" == strtoupper(substr($buff, strlen("HAVING") - strlen($buff)))) {
// add sequence (not including HAVING) to GROUP BY clause
$query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "HAVING")));
// change state
$state = $STATE_HAVING;
// clear sequence buffer
$buff = "";
} else {
if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) {
// add sequence (not including ORDER BY) to GROUP BY clause
$query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY")));
// change state
$state = $STATE_ORDER_BY;
// clear sequence buffer
$buff = "";
} else {
if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) {
// add sequence (not including LIMIT) to GROUP BY clause
$query->groupBy(substr($buff, 0, strpos(strtoupper($buff), "LIMIT")));
// change state
$state = $STATE_LIMIT;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to GROUP BY clause
$query->groupBy($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
}
}
break;
case $STATE_HAVING:
// Include entire HAVING clause from sql string to parse as a single element
// this simplifies the inclusion of logical operators in the sql string
// Subsequent HAVING clauses added with having() will be added to the query with AND operator
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ("ORDER BY" == strtoupper(substr($buff, strlen("ORDER BY") - strlen($buff)))) {
// add sequence (not including ORDER BY) to HAVING clause
$query->having(substr($buff, 0, strpos(strtoupper($buff), "ORDER BY")));
// change state
$state = $STATE_ORDER_BY;
// clear sequence buffer
$buff = "";
} else {
if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) {
// add sequence (not including LIMIT) to HAVING clause
$query->having(substr($buff, 0, strpos(strtoupper($buff), "LIMIT")));
// change state
$state = $STATE_LIMIT;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to HAVING clause
$query->having($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
break;
case $STATE_ORDER_BY:
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ($char == "," && $count_open_paren == 0 && $bool_open_single_quote == false && $bool_open_double_quote == false) {
// add sequence to ORDER BY cluase
$query->orderBy($buff);
// clear sequence buffer
$buff = "";
} else {
if ("LIMIT" == strtoupper(substr($buff, strlen("LIMIT") - strlen($buff)))) {
// add sequence (not including LIMIT) to ORDER BY clause
$query->orderBy(substr($buff, 0, strpos(strtoupper($buff), "LIMIT")));
// change state
$state = $STATE_LIMIT;
// clear sequence buffer
$buff = "";
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to ORDER BY clause
$query->orderBy($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
}
}
break;
case $STATE_LIMIT:
// trim leading spaces
if ($char == " " && strlen($buff) == 0) {
// skip character
continue;
} else {
if ($strlen == $i + 1) {
// add final character to sequence buffer
$buff .= $char;
// add sequence to LIMIT clause
$query->limit($buff);
} else {
// append current char to sequence buffer
$buff .= $char;
}
}
break;
}
// END: switch($state)
}
// END: for(all chars)
return $query;
}
protected function initReportQuery()
{
$tag = "ReportDrafter: initReportQuery()";
Log::debug("$tag");
// convenience pointers
$reportBP = $this->reportBlueprint;
$params = $this->params;
/*
// Build a query
*/
$queryString = $reportBP->getQuery();
// replace {variables} with $variables (from $params["data"])
if( array_key_exists("data", $params) )
{
// separate $params["data"] into parallel arrays
$data = $params["data"];
$find = array();
$replace = array();
foreach($data as $f=>$r)
{
$find[] = "{" . $f . "}";
$replace[] = $r;
}
$queryString = str_replace($find, $replace, $queryString);
}
// parse query string from blueprint
$query = SQLSelectQuery::parseSQL($queryString);
// where
if(array_key_exists("where", $this->params))
{
$query->where($this->params["where"]);
}
// process filters
// TODO
// order by
if(array_key_exists("order", $this->params))
{
$order = $this->params["order"];
if(is_array($order))
{
foreach($order as $o)
{
$query->orderBy($o);
}
}
else
{
$query->orderBy($order);
}
}
// LIMIT (PAGING)
if(array_key_exists("page", $this->params))
{
// Init page number
$page = 1;
if( (is_int($this->params["page"])) && ($this->params["page"]>=1) ) $page = $this->params["page"];
// Init page limit
$page_limit = 20; // default
if( (array_key_exists("page_limit", $this->params)) && (is_int((int) $this->params["page_limit"])) && ($this->params["page_limit"]>=1) ) $page_limit = $this->params["page_limit"];
else $this->params["page_limit"] = $page_limit; // init default for use by javascript
// Calculate OFFSET (First row offset=0)
$page_offset = ($page-1) * $page_limit;
// Count the total number of rows that this query will produce (without LIMITs)
$sql = new DatabaseQuery($query->toString());
try
{
$sql->doQuery();
$num_rows = $sql->get_num_rows();
Log::debug("$tag: num_rows = $num_rows");
// Save "count" to params (for use by javascript)
$this->params["count"] = (int) $num_rows;
}
catch(Exception $e)
{
Log::error("$tag: Caught: " . $e->getMessage());
}
// Add LIMIT clause to query
$query->limit("$page_offset, $page_limit");
}
$this->query = $query;
} // END: protected function initReportQuery()
<?php
require_once "ClassLoader.php";
echo "<h1>Testing SQLSelectQuery</h1>";
$sqlSelectQuery = SQLSelectQuery::parseSQL("SELECT id, login, passwd AS Secret FROM Member WHERE status='Active' AND id>'2' ORDER BY login ASC LIMIT 5");
echo $sqlSelectQuery->toString();
echo "<br/><br/>";
$sql = new SQLSelectQuery();
$sql->select("id");
$sql->select("member.name as name");
$sql->select("count(*) as num");
$sql->from("access");
$sql->leftjoin("member", "access.member_id", "member.id");
$sql->where("access.type='GET'");
$sql->having("num > 1");
$sql->groupBy("member.name");
$sql->orderBy("num DESC");
$sql->limit("25");
echo $sql->toString();