Newer
Older
* $what - (mixed) value to be sent to the database. No escaping is
* necessary. Pass a raw value here.
*
* Returns:
* (string) token to be placed into the compiled SQL statement. For
* MySQL, this is always the string '?'.
*/
function input($what, $slot=false) {
if ($what instanceof QuerySet) {
$q = $what->getQuery(array('nosort'=>true));
$this->params = array_merge($this->params, $q->params);
return $q->sql;
elseif ($what instanceof SqlFunction) {
return $what->toSql($this);
elseif (!isset($what)) {
return 'NULL';
}
$this->params[] = $what;
return ':'.(count($this->params));
}
function quote($what) {
return "`$what`";
}
/**
* getWhereClause
*
* This builds the WHERE ... part of a DML statement. This should be
* called before ::getJoins(), because it may add joins into the
* statement based on the relationships used in the where clause
*/
protected function getWhereHavingClause($queryset) {
$constraints = $this->compileConstraints($queryset->constraints, $model);
$where = $having = array();
foreach ($constraints as $C) {
if ($C->type == CompiledExpression::TYPE_WHERE)
$where[] = $C;
else
$having[] = $C;
}
if (isset($queryset->extra['where'])) {
foreach ($queryset->extra['where'] as $S) {
$where[] = "($S)";
}
}
$where = ' WHERE '.implode(' AND ', $where);
if ($having)
$having = ' HAVING '.implode(' AND ', $having);
return array($where ?: '', $having ?: '');
}
function compileCount($queryset) {
$model = $queryset->model;
$table = $model::$meta['table'];
list($where, $having) = $this->getWhereHavingClause($queryset);
$joins = $this->getJoins($queryset);
$sql = 'SELECT COUNT(*) AS count FROM '.$this->quote($table).$joins.$where;
$exec = new MysqlExecutor($sql, $this->params);
$row = $exec->getArray();
return $row['count'];
}
function compileSelect($queryset) {
$model = $queryset->model;
// Use an alias for the root model table
$this->joins[''] = array('alias' => ($rootAlias = $this->nextAlias()));
// Compile the WHERE clause
$this->annotations = $queryset->annotations ?: array();
list($where, $having) = $this->getWhereHavingClause($queryset);
// Compile the ORDER BY clause
if (($columns = $queryset->getSortFields()) && !isset($this->options['nosort'])) {
if ($sort instanceof SqlFunction) {
$field = $sort->toSql($this, $model);
}
else {
if ($sort[0] == '-') {
$dir = 'DESC';
$sort = substr($sort, 1);
}
list($field) = $this->getField($sort, $model);
// TODO: Throw exception if $field can be indentified as
// invalid
if ($field instanceof SqlFunction)
$field = $field->toSql($this, $model);
$orders[] = $field.' '.$dir;
}
$sort = ' ORDER BY '.implode(', ', $orders);
}
// Compile the field listing
$table = $this->quote($model::$meta['table']).' '.$rootAlias;
$defer = $queryset->defer ?: array();
// Add local fields first
$model::_inspect();
foreach ($model::$meta['fields'] as $f) {
// Handle deferreds
if (isset($defer[$f]))
continue;
$fields[$rootAlias . '.' . $this->quote($f)] = true;
$fieldMap[] = array($theseFields, $model);
// Add the JOINs to this query
foreach ($queryset->related as $sr) {
// XXX: Sort related by the paths so that the shortest paths
// are resolved first when building out the models.
$full_path = '';
$parts = array();
// Track each model traversal and fetch data for each of the
// models in the path of the related table
foreach (explode('__', $sr) as $field) {
$full_path .= $field;
$parts[] = $field;
list($alias, $fmodel) = $this->getField($full_path, $model,
array('table'=>true, 'model'=>true));
$fmodel::_inspect();
foreach ($fmodel::$meta['fields'] as $f) {
// Handle deferreds
if (isset($defer[$sr . '__' . $f]))
continue;
elseif (isset($fields[$alias.'.'.$this->quote($f)]))
continue;
$fields[$alias . '.' . $this->quote($f)] = true;
if ($theseFields) {
$fieldMap[] = array($theseFields, $fmodel, $parts);
}
$full_path .= '__';
}
}
}
// Support retrieving only a list of values rather than a model
elseif ($queryset->values) {
foreach ($queryset->values as $alias=>$v) {
list($f) = $this->getField($v, $model);
$fields[$f->toSql($this, $model, $alias)] = true;
else {
if (!is_int($alias))
$f .= ' AS '.$this->quote($alias);
// If there are annotations, add in these fields to the
// GROUP BY clause
if ($queryset->annotations)
$group_by[] = $unaliased;
}
// Simple selection from one table
else {
if ($queryset->defer) {
foreach ($model::$meta['fields'] as $f) {
if (isset($queryset->defer[$f]))
continue;
$fields[$rootAlias .'.'. $this->quote($f)] = true;
}
}
else {
$fields[$rootAlias.'.*'] = true;
$fields = array_keys($fields);
// Add in annotations
if ($queryset->annotations) {
// The root model will receive the annotations, add in the
// annotation after the root model's fields
$T = $A->toSql($this, $model, $alias);
if ($fieldMap) {
array_splice($fields, count($fieldMap[0][0]), 0, array($T));
$fieldMap[0][0][] = $A->getAlias();
}
else {
// No field map — just add to end of field list
$fields[] = $T;
}
// If no group by has been set yet, use the root model pk
if (!$group_by) {
foreach ($model::$meta['pk'] as $pk)
$group_by[] = $rootAlias .'.'. $pk;
}
// Add in SELECT extras
if (isset($queryset->extra['select'])) {
foreach ($queryset->extra['select'] as $name=>$expr) {
if ($expr instanceof SqlFunction)
$expr = $expr->toSql($this, false, $name);
$fields[] = $expr;
}
}
if (isset($queryset->distinct)) {
foreach ($queryset->distinct as $d)
list($group_by[]) = $this->getField($d, $model);
}
$group_by = $group_by ? ' GROUP BY '.implode(', ', $group_by) : '';
$joins = $this->getJoins($queryset);
$sql = 'SELECT '.implode(', ', $fields).' FROM '
.$table.$joins.$where.$group_by.$having.$sort;
if ($queryset->limit)
$sql .= ' LIMIT '.$queryset->limit;
if ($queryset->offset)
$sql .= ' OFFSET '.$queryset->offset;
switch ($queryset->lock) {
case QuerySet::LOCK_EXCLUSIVE:
$sql .= ' FOR UPDATE';
break;
case QuerySet::LOCK_SHARED:
$sql .= ' LOCK IN SHARE MODE';
break;
}
return new MysqlExecutor($sql, $this->params, $fieldMap);
function __compileUpdateSet($model, array $pk) {
$fields = array();
foreach ($model->dirty as $field=>$old) {
if ($model->__new__ or !in_array($field, $pk)) {
$fields[] = sprintf('%s = %s', $this->quote($field),
$this->input($model->get($field)));
}
}
return ' SET '.implode(', ', $fields);
}
function compileUpdate(VerySimpleModel $model) {
$pk = $model::$meta['pk'];
$sql = 'UPDATE '.$this->quote($model::$meta['table']);
$sql .= $this->__compileUpdateSet($model, $pk);
// Support PK updates
$criteria = array();
foreach ($pk as $f) {
$criteria[$f] = @$model->dirty[$f] ?: $model->get($f);
}
$sql .= ' WHERE '.$this->compileQ(new Q($criteria), $model);
$sql .= ' LIMIT 1';
return new MySqlExecutor($sql, $this->params);
}
function compileInsert(VerySimpleModel $model) {
$pk = $model::$meta['pk'];
$sql = 'INSERT INTO '.$this->quote($model::$meta['table']);
$sql .= $this->__compileUpdateSet($model, $pk);
return new MySqlExecutor($sql, $this->params);
function compileDelete($model) {
$table = $model::$meta['table'];
$where = ' WHERE '.implode(' AND ',
$this->compileConstraints(array(new Q($model->pk)), $model));
$sql = 'DELETE FROM '.$this->quote($table).$where.' LIMIT 1';
return new MySqlExecutor($sql, $this->params);
function compileBulkDelete($queryset) {
$model = $queryset->model;
$table = $model::$meta['table'];
list($where, $having) = $this->getWhereHavingClause($queryset);
$joins = $this->getJoins($queryset);
$sql = 'DELETE '.$this->quote($table).'.* FROM '
.$this->quote($table).$joins.$where;
return new MysqlExecutor($sql, $this->params);
function compileBulkUpdate($queryset, array $what) {
$model = $queryset->model;
$table = $model::$meta['table'];
$set = array();
foreach ($what as $field=>$value)
$set[] = sprintf('%s = %s', $this->quote($field), $this->input($value));
$set = implode(', ', $set);
list($where, $having) = $this->getWhereHavingClause($queryset);
$joins = $this->getJoins($queryset);
$sql = 'UPDATE '.$this->quote($table).$joins.' SET '.$set.$where;
return new MysqlExecutor($sql, $this->params);
}
// Returns meta data about the table used to build queries
function inspectTable($table) {
static $cache = array();
// XXX: Assuming schema is not changing — add support to track
// current schema
if (isset($cache[$table]))
return $cache[$table];
$sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS '
.'WHERE TABLE_NAME = '.db_input($table).' AND TABLE_SCHEMA = DATABASE() '
.'ORDER BY ORDINAL_POSITION';
$ex = new MysqlExecutor($sql, array());
$columns = array();
while (list($column) = $ex->getRow()) {
$columns[] = $column;
}
return $cache[$table] = $columns;
class MySqlExecutor {
var $stmt;
var $fields = array();
var $sql;
var $params;
// Array of [count, model] values representing which fields in the
// result set go with witch model. Useful for handling select_related
// queries
var $map;
function __construct($sql, $params, $map=null) {
$this->sql = $sql;
$this->params = $params;
$this->map = $map;
}
function getMap() {
return $this->map;
function fixupParams() {
$self = $this;
$params = array();
$sql = preg_replace_callback('/:(\d+)/', function($m) use ($self, &$params) {
$params[] = $self->params[$m[1]-1];
return '?';
}, $this->sql);
return array($sql, $params);
}
$this->execute();
$this->_setup_output();
}
function execute() {
list($sql, $params) = $this->fixupParams();
if (!($this->stmt = db_prepare($sql)))
throw new InconsistentModelException(
'Unable to prepare query: '.db_error().' '.$sql);
if (count($params))
$this->_bind($params);
if (!$this->stmt->execute() || ! $this->stmt->store_result()) {
throw new OrmException('Unable to execute query: ' . $this->stmt->error);
}
return true;
}
function _bind($params) {
if (count($params) != $this->stmt->param_count)
throw new Exception(__('Parameter count does not match query'));
if (is_int($p) || is_bool($p))
elseif (is_float($p))
$types .= 'd';
elseif ($p instanceof DateTime) {
$types .= 's';
$p = $p->format('Y-m-d h:i:s');
}
elseif (is_object($p)) {
$types .= 's';
$p = (string) $p;
}
// TODO: Emit error if param is null
array_unshift($ps, $types);
call_user_func_array(array($this->stmt,'bind_param'), $ps);
}
function _setup_output() {
if (!($meta = $this->stmt->result_metadata()))
throw new OrmException('Unable to fetch statment metadata: ', $this->stmt->error);
$this->fields = $meta->fetch_fields();
}
// Iterator interface
function rewind() {
if (!isset($this->stmt))
$this->_prepare();
$this->stmt->data_seek(0);
}
function next() {
$status = $this->stmt->fetch();
if ($status === false)
throw new OrmException($this->stmt->error);
elseif ($status === null) {
$this->close();
return false;
}
return true;
}
function getArray() {
$output = array();
$variables = array();
if (!isset($this->stmt))
$this->_prepare();
foreach ($this->fields as $f)
$variables[] = &$output[$f->name]; // pass by reference
if (!call_user_func_array(array($this->stmt, 'bind_result'), $variables))
throw new OrmException('Unable to bind result: ' . $this->stmt->error);
if (!$this->next())
return false;
return $output;
}
function getRow() {
$output = array();
$variables = array();
if (!isset($this->stmt))
$this->_prepare();
foreach ($this->fields as $f)
$variables[] = &$output[]; // pass by reference
if (!call_user_func_array(array($this->stmt, 'bind_result'), $variables))
throw new OrmException('Unable to bind result: ' . $this->stmt->error);
if (!$this->next())
return false;
return $output;
}
function close() {
if (!$this->stmt)
return;
$this->stmt->close();
$this->stmt = null;
}
function affected_rows() {
return $this->stmt->affected_rows;
}
function insert_id() {
return $this->stmt->insert_id;
}
return preg_replace_callback('/:(\d+)/', function($m) use ($self) {
$p = $self->params[$m[1]-1];
return db_real_escape($p, is_string($p));
}, $this->sql);
class Q implements Serializable {
const NEGATED = 0x0001;
const ANY = 0x0002;
var $constraints;
var $flags;
var $negated = false;
var $ored = false;
function __construct($filter=array(), $flags=0) {
if (!is_array($filter))
$filter = array($filter);
$this->constraints = $filter;
$this->negated = $flags & self::NEGATED;
$this->ored = $flags & self::ANY;
}
function isNegated() {
return $this->negated;
}
function isOred() {
return $this->ored;
}
function negate() {
$this->negated = !$this->negated;
return $this;
}
function union() {
$this->ored = true;
}
function add($constraints) {
if (is_array($constraints))
$this->constraints = array_merge($this->constraints, $constraints);
elseif ($constraints instanceof static)
$this->constraints[] = $constraints;
else
throw new InvalidArgumentException('Expected an instance of Q or an array thereof');
return $this;
}
static function not($constraints) {
return new static($constraints, self::NEGATED);
}
static function any($constraints) {
return new static($constraints, self::ANY);
function serialize() {
return serialize(array(
'f' =>
($this->negated ? self::NEGATED : 0)
| ($this->ored ? self::ANY : 0),
'c' => $this->constraints
));
}
function unserialize($data) {
$data = unserialize($data);
$this->constraints = $data['c'];
$this->ored = $data['f'] & self::ANY;
$this->negated = $data['f'] & self::NEGATED;
}