/**
* Interface for querying arbitrary date from various backends.
*
* Table::q('SELECT nickname FROM User'); // All User rows, but we only fetch the nickname column.
*
* Table::q('User'); // All User rows
*
* @param string $query
* @param array $vars
* @return TableSet Returns
*/
public static final function q($query, array $vars = array())
{
// Table::q('User WHERE id=?')
$p = new StringParser($query, TRUE);
$table = $p->consume();
$cols = FALSE;
if ($p->type === StringParser::CONSUMED_WORD && $table === 'SELECT') {
$cols = array();
// This is a special query, selecting only a specific column or more
while (TRUE) {
$col = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects string column names after SELECT keyword, separated by commas.");
}
$cols[] = $col;
$peek = $p->consume();
if ($p->type !== StringParser::CONSUMED_SYMBOLS || $peek !== ',') {
$p->push($peek);
break;
}
}
$from = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD || $from !== 'FROM') {
throw new QueryException("Expects FROM keyword after SELECT clause.");
}
$table = $p->consume();
}
if (!$table) {
throw new QueryException("Empty query");
}
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expected class name or SELECT keyword.");
}
if (!class_exists($table)) {
throw new QueryException("Class '" . $table . "' not found.");
}
if (!is_subclass_of($table, 'Table')) {
throw new QueryException("Class '" . $table . "' must extend Table");
}
$rows = $table::all();
if ($cols !== FALSE) {
$rows->fields(implode(",", $cols));
}
$wheres = array();
while ($word = $p->consume()) {
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects keyword after class name.");
}
switch ($word) {
case 'WHERE':
while (true) {
// Consume WHERE parts
$col = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects column name.");
}
// if(!in_array($col, $table::$_cols)) throw new QueryException("Column '".$col."' not found in '".$table."'.");
$op = $p->consume();
if ($p->type !== StringParser::CONSUMED_SYMBOLS) {
throw new QueryException("Expects operator.");
}
switch ($op) {
case '=?':
case '>?':
case '<?':
$rows->where($col, $op[0], array_shift($vars));
break;
case '<=?':
case '>=?':
$rows->where($col, $op[0] . $op[1], array_shift($vars));
break;
default:
throw new QueryException("Unknown operator '{$op}'.");
}
$peek = $p->consume();
if ($peek !== 'AND') {
$p->push($peek);
break;
}
}
break;
case 'LIMIT':
$limit = intval($p->consume());
$offset = FALSE;
if ($p->type !== StringParser::CONSUMED_INTEGER) {
throw new QueryException("Expects integer after LIMIT keyword.");
}
$peek = $p->consume();
if ($peek === ',') {
$offset = $limit;
$limit = intval($p->consume());
if ($p->type !== StringParser::CONSUMED_INTEGER) {
throw new QueryException("Expects integer offset and length after LIMIT keyword.");
}
} else {
$p->push($peek);
}
if ($offset !== FALSE) {
$rows->limit($limit, $offset);
} else {
$rows->limit($limit);
}
break;
case 'ORDER':
$col = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects column name after ORDER keyword.");
}
$peek = $p->consume();
if ($p->type === StringParser::CONSUMED_WORD && $peek === 'DESC') {
$rows->order($col, TRUE);
} else {
$p->push($peek);
$rows->order($col);
}
break;
default:
throw new QueryException('Unexpected keyword "' . $word . '" encountered. (Queries are case sensitive!)');
}
}
return $rows;
}
protected function _buildSql()
{
if (isset($this->_cache['buildSql'])) {
return $this->_cache['buildSql'];
}
global $config;
$_class = $this->_class;
$fields = array();
$tables = array();
list($wheres, $vars) = $this->_buildWheres();
if ($this->_fields === '*') {
$fields[] = $_class::$_table . '.*';
} else {
// Validate fields
if (!is_string($this->_fields)) {
throw new Exception("Fields must be specified as a string when querying " . $_class);
}
$parts = explode(",", $this->_fields);
foreach ($parts as $part) {
$part = trim($part);
if (!in_array($part, $_class::$_cols)) {
throw new Exception("Unknown column " . $part . " in " . $_class);
}
$fields[] = $_class::$_table . '.' . $part;
}
}
if (sizeof($this->_joins) > 0) {
$joinNum = 1;
foreach ($this->_joins as $join) {
$p = new StringParser($join[0], TRUE);
$nextVar = function () use($p, $_class) {
$res = array();
$a = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects a [column name] or [table name].[column name] in join statement.");
}
$dot = $p->consume();
if ($p->type === StringParser::CONSUMED_SYMBOLS && $dot === '.') {
$col = $p->consume();
if ($p->type !== StringParser::CONSUMED_WORD) {
throw new QueryException("Expects a column name after '{$a}.' in join statement.");
}
if (!in_array($col, $a::$_cols)) {
throw new QueryException("Unknown column '{$a}.{$col}' in join statement.");
}
return array($a, $col);
} else {
$p->push($dot);
return $a;
}
};
$joinWheres = array();
while (TRUE) {
$part1 = $nextVar();
$eq = $p->consume();
if ($p->type !== StringParser::CONSUMED_SYMBOLS || $eq !== '=' && $eq !== '=?') {
throw new QueryException("Expects the '=' or '=?' operator in join statement, got '{$eq}'.");
}
$toAdd = array();
if ($eq === '=') {
$part2 = $nextVar();
if (is_array($part1)) {
$joinWheres[] = array($part1, $part2);
} else {
$joinWheres[] = array($part2, $part1);
}
} else {
$joinWheres[] = array($part1, '?', array_shift($join[1]));
}
$peek = $p->consume();
if ($peek === FALSE) {
break;
}
if ($p->type !== StringParser::CONSUMED_WORD || $peek !== 'AND') {
throw new QueryException("Unexpected '{$peek}' in join statement. Expected 'AND' or nothing.");
}
}
// Check that this join does not use multiple tables. Can't do that without calling ->join multiple times
$joinTables = array();
foreach ($joinWheres as $joinWhere) {
if (is_array($joinWhere[0])) {
$joinTables[$joinWhere[0][0]] = true;
}
}
if (sizeof($joinTables) !== 1) {
throw new QueryException("Can't use a single join that spans multiple tables (" . implode(",", array_keys($tables)) . ").");
}
// joinSpec should be fine
$arrayKeys = array_keys($joinTables);
$joinClass = array_shift($arrayKeys);
$tables[] = $joinClass::$_table . ' AS t' . $joinNum;
$joinFields = $join[sizeof($join) - 1];
foreach ($joinFields as $from => $to) {
if (!in_array($from, $joinClass::$_cols)) {
throw new QueryException("Column '{$from}' not found in class '{$joinClass}'.");
}
$fields[] = 't' . $joinNum . '.' . $from . ' AS ' . $to;
}
foreach ($joinWheres as $joinWhere) {
if ($joinWhere[1] === '?') {
$wheres[] = 't' . $joinNum . '.' . $joinWhere[0][1] . '=?';
$vars[] = $joinWhere[2];
} else {
$wheres[] = 't' . $joinNum . '.' . $joinWhere[0][1] . '=' . $_class::$_table . '.' . $joinWhere[1];
}
}
}
$joinNum++;
}
$sql = 'SELECT ' . implode(",", $fields) . ' FROM ' . $_class::$_table;
if (sizeof($tables) > 0) {
$sql .= ',' . implode(",", $tables);
}
if (sizeof($wheres) > 0) {
$sql .= ' WHERE ' . implode(" AND ", $wheres);
}
if ($this->_order) {
if (!in_array($this->_order, $_class::$_cols)) {
throw new Exception("Unknown column " . $where[0] . " in " . $_class);
}
$sql .= ' ORDER BY ' . $_class::$_table . '.' . $this->_order;
if ($this->_orderDesc) {
$sql .= ' DESC';
}
}
if (!$this->_noLimit) {
if ($this->_limit < 1) {
throw new Exception("The limit must be at least 1 when querying " . $_class);
}
if ($this->_offset < 0) {
throw new Exception("The offset must not be negative when querying " . $_class);
}
$sql .= ' LIMIT ' . $this->_offset . ',' . $this->_limit;
}
return $this->_cache['buildSql'] = array($sql, $vars);
}
