/** * 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); }