diff options
Diffstat (limited to 'core/includes/database/select.inc')
| -rw-r--r-- | core/includes/database/select.inc | 1630 |
1 files changed, 1630 insertions, 0 deletions
diff --git a/core/includes/database/select.inc b/core/includes/database/select.inc new file mode 100644 index 00000000000..75047785493 --- /dev/null +++ b/core/includes/database/select.inc @@ -0,0 +1,1630 @@ +<?php + +/** + * @ingroup database + * @{ + */ + +require_once __DIR__ . '/query.inc'; + +/** + * Interface for extendable query objects. + * + * "Extenders" follow the "Decorator" OOP design pattern. That is, they wrap + * and "decorate" another object. In our case, they implement the same interface + * as select queries and wrap a select query, to which they delegate almost all + * operations. Subclasses of this class may implement additional methods or + * override existing methods as appropriate. Extenders may also wrap other + * extender objects, allowing for arbitrarily complex "enhanced" queries. + */ +interface QueryExtendableInterface { + + /** + * Enhance this object by wrapping it in an extender object. + * + * @param $extender_name + * The base name of the extending class. The base name will be checked + * against the current database connection to allow driver-specific subclasses + * as well, using the same logic as the query objects themselves. For example, + * PagerDefault_mysql is the MySQL-specific override for PagerDefault. + * @return QueryExtendableInterface + * The extender object, which now contains a reference to this object. + */ + public function extend($extender_name); +} + +/** + * Interface definition for a Select Query object. + */ +interface SelectQueryInterface extends QueryConditionInterface, QueryAlterableInterface, QueryExtendableInterface, QueryPlaceholderInterface { + + /* Alter accessors to expose the query data to alter hooks. */ + + /** + * Returns a reference to the fields array for this query. + * + * Because this method returns by reference, alter hooks may edit the fields + * array directly to make their changes. If just adding fields, however, the + * use of addField() is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getFields(); + * @endcode + * + * @return + * A reference to the fields array structure. + */ + public function &getFields(); + + /** + * Returns a reference to the expressions array for this query. + * + * Because this method returns by reference, alter hooks may edit the expressions + * array directly to make their changes. If just adding expressions, however, the + * use of addExpression() is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getExpressions(); + * @endcode + * + * @return + * A reference to the expression array structure. + */ + public function &getExpressions(); + + /** + * Returns a reference to the order by array for this query. + * + * Because this method returns by reference, alter hooks may edit the order-by + * array directly to make their changes. If just adding additional ordering + * fields, however, the use of orderBy() is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getOrderBy(); + * @endcode + * + * @return + * A reference to the expression array structure. + */ + public function &getOrderBy(); + + /** + * Returns a reference to the group-by array for this query. + * + * Because this method returns by reference, alter hooks may edit the group-by + * array directly to make their changes. If just adding additional grouping + * fields, however, the use of groupBy() is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getGroupBy(); + * @endcode + * + * @return + * A reference to the group-by array structure. + */ + public function &getGroupBy(); + + /** + * Returns a reference to the tables array for this query. + * + * Because this method returns by reference, alter hooks may edit the tables + * array directly to make their changes. If just adding tables, however, the + * use of the join() methods is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getTables(); + * @endcode + * + * @return + * A reference to the tables array structure. + */ + public function &getTables(); + + /** + * Returns a reference to the union queries for this query. This include + * queries for UNION, UNION ALL, and UNION DISTINCT. + * + * Because this method returns by reference, alter hooks may edit the tables + * array directly to make their changes. If just adding union queries, + * however, the use of the union() method is preferred. + * + * Note that this method must be called by reference as well: + * + * @code + * $fields =& $query->getUnion(); + * @endcode + * + * @return + * A reference to the union query array structure. + */ + public function &getUnion(); + + /** + * Compiles and returns an associative array of the arguments for this prepared statement. + * + * @param $queryPlaceholder + * When collecting the arguments of a subquery, the main placeholder + * object should be passed as this parameter. + * + * @return + * An associative array of all placeholder arguments for this query. + */ + public function getArguments(QueryPlaceholderInterface $queryPlaceholder = NULL); + + /* Query building operations */ + + /** + * Sets this query to be DISTINCT. + * + * @param $distinct + * TRUE to flag this query DISTINCT, FALSE to disable it. + * @return SelectQueryInterface + * The called object. + */ + public function distinct($distinct = TRUE); + + /** + * Adds a field to the list to be SELECTed. + * + * @param $table_alias + * The name of the table from which the field comes, as an alias. Generally + * you will want to use the return value of join() here to ensure that it is + * valid. + * @param $field + * The name of the field. + * @param $alias + * The alias for this field. If not specified, one will be generated + * automatically based on the $table_alias and $field. The alias will be + * checked for uniqueness, so the requested alias may not be the alias + * that is assigned in all cases. + * @return + * The unique alias that was assigned for this field. + */ + public function addField($table_alias, $field, $alias = NULL); + + /** + * Add multiple fields from the same table to be SELECTed. + * + * This method does not return the aliases set for the passed fields. In the + * majority of cases that is not a problem, as the alias will be the field + * name. However, if you do need to know the alias you can call getFields() + * and examine the result to determine what alias was created. Alternatively, + * simply use addField() for the few fields you care about and this method for + * the rest. + * + * @param $table_alias + * The name of the table from which the field comes, as an alias. Generally + * you will want to use the return value of join() here to ensure that it is + * valid. + * @param $fields + * An indexed array of fields present in the specified table that should be + * included in this query. If not specified, $table_alias.* will be generated + * without any aliases. + * @return SelectQueryInterface + * The called object. + */ + public function fields($table_alias, array $fields = array()); + + /** + * Adds an expression to the list of "fields" to be SELECTed. + * + * An expression can be any arbitrary string that is valid SQL. That includes + * various functions, which may in some cases be database-dependent. This + * method makes no effort to correct for database-specific functions. + * + * @param $expression + * The expression string. May contain placeholders. + * @param $alias + * The alias for this expression. If not specified, one will be generated + * automatically in the form "expression_#". The alias will be checked for + * uniqueness, so the requested alias may not be the alias that is assigned + * in all cases. + * @param $arguments + * Any placeholder arguments needed for this expression. + * @return + * The unique alias that was assigned for this expression. + */ + public function addExpression($expression, $alias = NULL, $arguments = array()); + + /** + * Default Join against another table in the database. + * + * This method is a convenience method for innerJoin(). + * + * @param $table + * The table against which to join. + * @param $alias + * The alias for the table. In most cases this should be the first letter + * of the table, or the first letter of each "word" in the table. + * @param $condition + * The condition on which to join this table. If the join requires values, + * this clause should use a named placeholder and the value or values to + * insert should be passed in the 4th parameter. For the first table joined + * on a query, this value is ignored as the first table is taken as the base + * table. The token %alias can be used in this string to be replaced with + * the actual alias. This is useful when $alias is modified by the database + * system, for example, when joining the same table more than once. + * @param $arguments + * An array of arguments to replace into the $condition of this join. + * @return + * The unique alias that was assigned for this table. + */ + public function join($table, $alias = NULL, $condition = NULL, $arguments = array()); + + /** + * Inner Join against another table in the database. + * + * @param $table + * The table against which to join. + * @param $alias + * The alias for the table. In most cases this should be the first letter + * of the table, or the first letter of each "word" in the table. + * @param $condition + * The condition on which to join this table. If the join requires values, + * this clause should use a named placeholder and the value or values to + * insert should be passed in the 4th parameter. For the first table joined + * on a query, this value is ignored as the first table is taken as the base + * table. The token %alias can be used in this string to be replaced with + * the actual alias. This is useful when $alias is modified by the database + * system, for example, when joining the same table more than once. + * @param $arguments + * An array of arguments to replace into the $condition of this join. + * @return + * The unique alias that was assigned for this table. + */ + public function innerJoin($table, $alias = NULL, $condition = NULL, $arguments = array()); + + /** + * Left Outer Join against another table in the database. + * + * @param $table + * The table against which to join. + * @param $alias + * The alias for the table. In most cases this should be the first letter + * of the table, or the first letter of each "word" in the table. + * @param $condition + * The condition on which to join this table. If the join requires values, + * this clause should use a named placeholder and the value or values to + * insert should be passed in the 4th parameter. For the first table joined + * on a query, this value is ignored as the first table is taken as the base + * table. The token %alias can be used in this string to be replaced with + * the actual alias. This is useful when $alias is modified by the database + * system, for example, when joining the same table more than once. + * @param $arguments + * An array of arguments to replace into the $condition of this join. + * @return + * The unique alias that was assigned for this table. + */ + public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = array()); + + /** + * Right Outer Join against another table in the database. + * + * @param $table + * The table against which to join. + * @param $alias + * The alias for the table. In most cases this should be the first letter + * of the table, or the first letter of each "word" in the table. + * @param $condition + * The condition on which to join this table. If the join requires values, + * this clause should use a named placeholder and the value or values to + * insert should be passed in the 4th parameter. For the first table joined + * on a query, this value is ignored as the first table is taken as the base + * table. The token %alias can be used in this string to be replaced with + * the actual alias. This is useful when $alias is modified by the database + * system, for example, when joining the same table more than once. + * @param $arguments + * An array of arguments to replace into the $condition of this join. + * @return + * The unique alias that was assigned for this table. + */ + public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = array()); + + /** + * Join against another table in the database. + * + * This method does the "hard" work of queuing up a table to be joined against. + * In some cases, that may include dipping into the Schema API to find the necessary + * fields on which to join. + * + * @param $type + * The type of join. Typically one one of INNER, LEFT OUTER, and RIGHT OUTER. + * @param $table + * The table against which to join. May be a string or another SelectQuery + * object. If a query object is passed, it will be used as a subselect. + * @param $alias + * The alias for the table. In most cases this should be the first letter + * of the table, or the first letter of each "word" in the table. If omitted, + * one will be dynamically generated. + * @param $condition + * The condition on which to join this table. If the join requires values, + * this clause should use a named placeholder and the value or values to + * insert should be passed in the 4th parameter. For the first table joined + * on a query, this value is ignored as the first table is taken as the base + * table. The token %alias can be used in this string to be replaced with + * the actual alias. This is useful when $alias is modified by the database + * system, for example, when joining the same table more than once. + * @param $arguments + * An array of arguments to replace into the $condition of this join. + * @return + * The unique alias that was assigned for this table. + */ + public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()); + + /** + * Orders the result set by a given field. + * + * If called multiple times, the query will order by each specified field in the + * order this method is called. + * + * If the query uses DISTINCT or GROUP BY conditions, fields or expressions + * that are used for the order must be selected to be compatible with some + * databases like PostgreSQL. The PostgreSQL driver can handle simple cases + * automatically but it is suggested to explicitly specify them. Additionally, + * when ordering on an alias, the alias must be added before orderBy() is + * called. + * + * @param $field + * The field on which to order. + * @param $direction + * The direction to sort. Legal values are "ASC" and "DESC". + * @return SelectQueryInterface + * The called object. + */ + public function orderBy($field, $direction = 'ASC'); + + /** + * Orders the result set by a random value. + * + * This may be stacked with other orderBy() calls. If so, the query will order + * by each specified field, including this one, in the order called. Although + * this method may be called multiple times on the same query, doing so + * is not particularly useful. + * + * Note: The method used by most drivers may not scale to very large result + * sets. If you need to work with extremely large data sets, you may create + * your own database driver by subclassing off of an existing driver and + * implementing your own randomization mechanism. See + * + * http://jan.kneschke.de/projects/mysql/order-by-rand/ + * + * for an example of such an alternate sorting mechanism. + * + * @return SelectQueryInterface + * The called object + */ + public function orderRandom(); + + /** + * Restricts a query to a given range in the result set. + * + * If this method is called with no parameters, will remove any range + * directives that have been set. + * + * @param $start + * The first record from the result set to return. If NULL, removes any + * range directives that are set. + * @param $length + * The number of records to return from the result set. + * @return SelectQueryInterface + * The called object. + */ + public function range($start = NULL, $length = NULL); + + /** + * Add another Select query to UNION to this one. + * + * Union queries consist of two or more queries whose + * results are effectively concatenated together. Queries + * will be UNIONed in the order they are specified, with + * this object's query coming first. Duplicate columns will + * be discarded. All forms of UNION are supported, using + * the second '$type' argument. + * + * Note: All queries UNIONed together must have the same + * field structure, in the same order. It is up to the + * caller to ensure that they match properly. If they do + * not, an SQL syntax error will result. + * + * @param $query + * The query to UNION to this query. + * @param $type + * The type of UNION to add to the query. Defaults to plain + * UNION. + * @return SelectQueryInterface + * The called object. + */ + public function union(SelectQueryInterface $query, $type = ''); + + /** + * Groups the result set by the specified field. + * + * @param $field + * The field on which to group. This should be the field as aliased. + * @return SelectQueryInterface + * The called object. + */ + public function groupBy($field); + + /** + * Get the equivalent COUNT query of this query as a new query object. + * + * @return SelectQueryInterface + * A new SelectQuery object with no fields or expressions besides COUNT(*). + */ + public function countQuery(); + + /** + * Indicates if preExecute() has already been called on that object. + * + * @return + * TRUE is this query has already been prepared, FALSE otherwise. + */ + public function isPrepared(); + + /** + * Generic preparation and validation for a SELECT query. + * + * @return + * TRUE if the validation was successful, FALSE if not. + */ + public function preExecute(SelectQueryInterface $query = NULL); + + /** + * Helper function to build most common HAVING conditional clauses. + * + * This method can take a variable number of parameters. If called with two + * parameters, they are taken as $field and $value with $operator having a value + * of IN if $value is an array and = otherwise. + * + * @param $field + * The name of the field to check. If you would like to add a more complex + * condition involving operators or functions, use having(). + * @param $value + * The value to test the field against. In most cases, this is a scalar. For more + * complex options, it is an array. The meaning of each element in the array is + * dependent on the $operator. + * @param $operator + * The comparison operator, such as =, <, or >=. It also accepts more complex + * options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is an array + * = otherwise. + * @return QueryConditionInterface + * The called object. + */ + public function havingCondition($field, $value = NULL, $operator = NULL); + + /** + * Clone magic method. + * + * Select queries have dependent objects that must be deep-cloned. The + * connection object itself, however, should not be cloned as that would + * duplicate the connection itself. + */ + public function __clone(); + + /** + * Add FOR UPDATE to the query. + * + * FOR UPDATE prevents the rows retrieved by the SELECT statement from being + * modified or deleted by other transactions until the current transaction + * ends. Other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE + * of these rows will be blocked until the current transaction ends. + * + * @param $set + * IF TRUE, FOR UPDATE will be added to the query, if FALSE then it won't. + * + * @return QueryConditionInterface + * The called object. + */ + public function forUpdate($set = TRUE); +} + +/** + * The base extender class for Select queries. + */ +class SelectQueryExtender implements SelectQueryInterface { + + /** + * The SelectQuery object we are extending/decorating. + * + * @var SelectQueryInterface + */ + protected $query; + + /** + * The connection object on which to run this query. + * + * @var DatabaseConnection + */ + protected $connection; + + /** + * A unique identifier for this query object. + */ + protected $uniqueIdentifier; + + /** + * The placeholder counter. + */ + protected $placeholder = 0; + + public function __construct(SelectQueryInterface $query, DatabaseConnection $connection) { + $this->uniqueIdentifier = uniqid('', TRUE); + $this->query = $query; + $this->connection = $connection; + } + + /** + * Implements QueryPlaceholderInterface::uniqueIdentifier(). + */ + public function uniqueIdentifier() { + return $this->uniqueIdentifier; + } + + /** + * Implements QueryPlaceholderInterface::nextPlaceholder(). + */ + public function nextPlaceholder() { + return $this->placeholder++; + } + + /* Implementations of QueryAlterableInterface. */ + + public function addTag($tag) { + $this->query->addTag($tag); + return $this; + } + + public function hasTag($tag) { + return $this->query->hasTag($tag); + } + + public function hasAllTags() { + return call_user_func_array(array($this->query, 'hasAllTags'), func_get_args()); + } + + public function hasAnyTag() { + return call_user_func_array(array($this->query, 'hasAnyTags'), func_get_args()); + } + + public function addMetaData($key, $object) { + $this->query->addMetaData($key, $object); + return $this; + } + + public function getMetaData($key) { + return $this->query->getMetaData($key); + } + + /* Implementations of QueryConditionInterface for the WHERE clause. */ + + public function condition($field, $value = NULL, $operator = NULL) { + $this->query->condition($field, $value, $operator); + return $this; + } + + public function &conditions() { + return $this->query->conditions(); + } + + public function arguments() { + return $this->query->arguments(); + } + + public function where($snippet, $args = array()) { + $this->query->where($snippet, $args); + return $this; + } + + public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) { + return $this->query->compile($connection, $queryPlaceholder); + } + + public function compiled() { + return $this->query->compiled(); + } + + /* Implementations of QueryConditionInterface for the HAVING clause. */ + + public function havingCondition($field, $value = NULL, $operator = '=') { + $this->query->condition($field, $value, $operator, $num_args); + return $this; + } + + public function &havingConditions() { + return $this->having->conditions(); + } + + public function havingArguments() { + return $this->having->arguments(); + } + + public function having($snippet, $args = array()) { + $this->query->having($snippet, $args); + return $this; + } + + public function havingCompile(DatabaseConnection $connection) { + return $this->query->havingCompile($connection); + } + + /* Implementations of QueryExtendableInterface. */ + + public function extend($extender_name) { + // The extender can be anywhere so this needs to go to the registry, which + // is surely loaded by now. + $class = $this->connection->getDriverClass($extender_name, array(), TRUE); + return new $class($this, $this->connection); + } + + /* Alter accessors to expose the query data to alter hooks. */ + + public function &getFields() { + return $this->query->getFields(); + } + + public function &getExpressions() { + return $this->query->getExpressions(); + } + + public function &getOrderBy() { + return $this->query->getOrderBy(); + } + + public function &getGroupBy() { + return $this->query->getGroupBy(); + } + + public function &getTables() { + return $this->query->getTables(); + } + + public function &getUnion() { + return $this->query->getUnion(); + } + + public function getArguments(QueryPlaceholderInterface $queryPlaceholder = NULL) { + return $this->query->getArguments($queryPlaceholder); + } + + public function isPrepared() { + return $this->query->isPrepared(); + } + + public function preExecute(SelectQueryInterface $query = NULL) { + // If no query object is passed in, use $this. + if (!isset($query)) { + $query = $this; + } + + return $this->query->preExecute($query); + } + + public function execute() { + // By calling preExecute() here, we force it to preprocess the extender + // object rather than just the base query object. That means + // hook_query_alter() gets access to the extended object. + if (!$this->preExecute($this)) { + return NULL; + } + + return $this->query->execute(); + } + + public function distinct($distinct = TRUE) { + $this->query->distinct($distinct); + return $this; + } + + public function addField($table_alias, $field, $alias = NULL) { + return $this->query->addField($table_alias, $field, $alias); + } + + public function fields($table_alias, array $fields = array()) { + $this->query->fields($table_alias, $fields); + return $this; + } + + public function addExpression($expression, $alias = NULL, $arguments = array()) { + return $this->query->addExpression($expression, $alias, $arguments); + } + + public function join($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->query->join($table, $alias, $condition, $arguments); + } + + public function innerJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->query->innerJoin($table, $alias, $condition, $arguments); + } + + public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->query->leftJoin($table, $alias, $condition, $arguments); + } + + public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->query->rightJoin($table, $alias, $condition, $arguments); + } + + public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->query->addJoin($type, $table, $alias, $condition, $arguments); + } + + public function orderBy($field, $direction = 'ASC') { + $this->query->orderBy($field, $direction); + return $this; + } + + public function orderRandom() { + $this->query->orderRandom(); + return $this; + } + + public function range($start = NULL, $length = NULL) { + $this->query->range($start, $length); + return $this; + } + + public function union(SelectQueryInterface $query, $type = '') { + $this->query->union($query, $type); + return $this; + } + + public function groupBy($field) { + $this->query->groupBy($field); + return $this; + } + + public function forUpdate($set = TRUE) { + $this->query->forUpdate($set); + return $this; + } + + public function countQuery() { + // Create our new query object that we will mutate into a count query. + $count = clone($this); + + // Zero-out existing fields and expressions. + $fields =& $count->getFields(); + $fields = array(); + $expressions =& $count->getExpressions(); + $expressions = array(); + + // Also remove 'all_fields' statements, which are expanded into tablename.* + // when the query is executed. + $tables = &$count->getTables(); + foreach ($tables as $alias => &$table) { + unset($table['all_fields']); + } + + // Ordering a count query is a waste of cycles, and breaks on some + // databases anyway. + $orders = &$count->getOrderBy(); + $orders = array(); + + // COUNT() is an expression, so we add that back in. + $count->addExpression('COUNT(*)'); + + return $count; + } + + function isNull($field) { + $this->query->isNull($field); + return $this; + } + + function isNotNull($field) { + $this->query->isNotNull($field); + return $this; + } + + public function exists(SelectQueryInterface $select) { + $this->query->exists($select); + return $this; + } + + public function notExists(SelectQueryInterface $select) { + $this->query->notExists($select); + return $this; + } + + public function __toString() { + return (string) $this->query; + } + + public function __clone() { + $this->uniqueIdentifier = uniqid('', TRUE); + + // We need to deep-clone the query we're wrapping, which in turn may + // deep-clone other objects. Exciting! + $this->query = clone($this->query); + } + + /** + * Magic override for undefined methods. + * + * If one extender extends another extender, then methods in the inner extender + * will not be exposed on the outer extender. That's because we cannot know + * in advance what those methods will be, so we cannot provide wrapping + * implementations as we do above. Instead, we use this slower catch-all method + * to handle any additional methods. + */ + public function __call($method, $args) { + $return = call_user_func_array(array($this->query, $method), $args); + + // Some methods will return the called object as part of a fluent interface. + // Others will return some useful value. If it's a value, then the caller + // probably wants that value. If it's the called object, then we instead + // return this object. That way we don't "lose" an extender layer when + // chaining methods together. + if ($return instanceof SelectQueryInterface) { + return $this; + } + else { + return $return; + } + } +} + +/** + * Query builder for SELECT statements. + */ +class SelectQuery extends Query implements SelectQueryInterface { + + /** + * The fields to SELECT. + * + * @var array + */ + protected $fields = array(); + + /** + * The expressions to SELECT as virtual fields. + * + * @var array + */ + protected $expressions = array(); + + /** + * The tables against which to JOIN. + * + * This property is a nested array. Each entry is an array representing + * a single table against which to join. The structure of each entry is: + * + * array( + * 'type' => $join_type (one of INNER, LEFT OUTER, RIGHT OUTER), + * 'table' => $table, + * 'alias' => $alias_of_the_table, + * 'condition' => $condition_clause_on_which_to_join, + * 'arguments' => $array_of_arguments_for_placeholders_in_the condition. + * 'all_fields' => TRUE to SELECT $alias.*, FALSE or NULL otherwise. + * ) + * + * If $table is a string, it is taken as the name of a table. If it is + * a SelectQuery object, it is taken as a subquery. + * + * @var array + */ + protected $tables = array(); + + /** + * The fields by which to order this query. + * + * This is an associative array. The keys are the fields to order, and the value + * is the direction to order, either ASC or DESC. + * + * @var array + */ + protected $order = array(); + + /** + * The fields by which to group. + * + * @var array + */ + protected $group = array(); + + /** + * The conditional object for the WHERE clause. + * + * @var DatabaseCondition + */ + protected $where; + + /** + * The conditional object for the HAVING clause. + * + * @var DatabaseCondition + */ + protected $having; + + /** + * Whether or not this query should be DISTINCT + * + * @var boolean + */ + protected $distinct = FALSE; + + /** + * The range limiters for this query. + * + * @var array + */ + protected $range; + + /** + * An array whose elements specify a query to UNION, and the UNION type. The + * 'type' key may be '', 'ALL', or 'DISTINCT' to represent a 'UNION', + * 'UNION ALL', or 'UNION DISTINCT' statement, respectively. + * + * All entries in this array will be applied from front to back, with the + * first query to union on the right of the original query, the second union + * to the right of the first, etc. + * + * @var array + */ + protected $union = array(); + + /** + * Indicates if preExecute() has already been called. + * @var boolean + */ + protected $prepared = FALSE; + + /** + * The FOR UPDATE status + */ + protected $forUpdate = FALSE; + + public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) { + $options['return'] = Database::RETURN_STATEMENT; + parent::__construct($connection, $options); + $this->where = new DatabaseCondition('AND'); + $this->having = new DatabaseCondition('AND'); + $this->addJoin(NULL, $table, $alias); + } + + /* Implementations of QueryAlterableInterface. */ + + public function addTag($tag) { + $this->alterTags[$tag] = 1; + return $this; + } + + public function hasTag($tag) { + return isset($this->alterTags[$tag]); + } + + public function hasAllTags() { + return !(boolean)array_diff(func_get_args(), array_keys($this->alterTags)); + } + + public function hasAnyTag() { + return (boolean)array_intersect(func_get_args(), array_keys($this->alterTags)); + } + + public function addMetaData($key, $object) { + $this->alterMetaData[$key] = $object; + return $this; + } + + public function getMetaData($key) { + return isset($this->alterMetaData[$key]) ? $this->alterMetaData[$key] : NULL; + } + + /* Implementations of QueryConditionInterface for the WHERE clause. */ + + public function condition($field, $value = NULL, $operator = NULL) { + $this->where->condition($field, $value, $operator); + return $this; + } + + public function &conditions() { + return $this->where->conditions(); + } + + public function arguments() { + if (!$this->compiled()) { + return NULL; + } + + $args = $this->where->arguments() + $this->having->arguments(); + + foreach ($this->tables as $table) { + if ($table['arguments']) { + $args += $table['arguments']; + } + // If this table is a subquery, grab its arguments recursively. + if ($table['table'] instanceof SelectQueryInterface) { + $args += $table['table']->arguments(); + } + } + + foreach ($this->expressions as $expression) { + if ($expression['arguments']) { + $args += $expression['arguments']; + } + } + + // If there are any dependent queries to UNION, + // incorporate their arguments recursively. + foreach ($this->union as $union) { + $args += $union['query']->arguments(); + } + + return $args; + } + + public function where($snippet, $args = array()) { + $this->where->where($snippet, $args); + return $this; + } + + public function isNull($field) { + $this->where->isNull($field); + return $this; + } + + public function isNotNull($field) { + $this->where->isNotNull($field); + return $this; + } + + public function exists(SelectQueryInterface $select) { + $this->where->exists($select); + return $this; + } + + public function notExists(SelectQueryInterface $select) { + $this->where->notExists($select); + return $this; + } + + public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) { + $this->where->compile($connection, $queryPlaceholder); + $this->having->compile($connection, $queryPlaceholder); + + foreach ($this->tables as $table) { + // If this table is a subquery, compile it recursively. + if ($table['table'] instanceof SelectQueryInterface) { + $table['table']->compile($connection, $queryPlaceholder); + } + } + + // If there are any dependent queries to UNION, compile it recursively. + foreach ($this->union as $union) { + $union['query']->compile($connection, $queryPlaceholder); + } + } + + public function compiled() { + if (!$this->where->compiled() || !$this->having->compiled()) { + return FALSE; + } + + foreach ($this->tables as $table) { + // If this table is a subquery, check its status recursively. + if ($table['table'] instanceof SelectQueryInterface) { + if (!$table['table']->compiled()) { + return FALSE; + } + } + } + + foreach ($this->union as $union) { + if (!$union['query']->compiled()) { + return FALSE; + } + } + + return TRUE; + } + + /* Implementations of QueryConditionInterface for the HAVING clause. */ + + public function havingCondition($field, $value = NULL, $operator = NULL) { + $this->having->condition($field, $value, $operator); + return $this; + } + + public function &havingConditions() { + return $this->having->conditions(); + } + + public function havingArguments() { + return $this->having->arguments(); + } + + public function having($snippet, $args = array()) { + $this->having->where($snippet, $args); + return $this; + } + + public function havingCompile(DatabaseConnection $connection) { + return $this->having->compile($connection, $this); + } + + /* Implementations of QueryExtendableInterface. */ + + public function extend($extender_name) { + $override_class = $extender_name . '_' . $this->connection->driver(); + if (class_exists($override_class)) { + $extender_name = $override_class; + } + return new $extender_name($this, $this->connection); + } + + public function havingIsNull($field) { + $this->having->isNull($field); + return $this; + } + + public function havingIsNotNull($field) { + $this->having->isNotNull($field); + return $this; + } + + public function havingExists(SelectQueryInterface $select) { + $this->having->exists($select); + return $this; + } + + public function havingNotExists(SelectQueryInterface $select) { + $this->having->notExists($select); + return $this; + } + + public function forUpdate($set = TRUE) { + if (isset($set)) { + $this->forUpdate = $set; + } + return $this; + } + + /* Alter accessors to expose the query data to alter hooks. */ + + public function &getFields() { + return $this->fields; + } + + public function &getExpressions() { + return $this->expressions; + } + + public function &getOrderBy() { + return $this->order; + } + + public function &getGroupBy() { + return $this->group; + } + + public function &getTables() { + return $this->tables; + } + + public function &getUnion() { + return $this->union; + } + + public function getArguments(QueryPlaceholderInterface $queryPlaceholder = NULL) { + if (!isset($queryPlaceholder)) { + $queryPlaceholder = $this; + } + $this->compile($this->connection, $queryPlaceholder); + return $this->arguments(); + } + + /** + * Indicates if preExecute() has already been called on that object. + */ + public function isPrepared() { + return $this->prepared; + } + + /** + * Generic preparation and validation for a SELECT query. + * + * @return + * TRUE if the validation was successful, FALSE if not. + */ + public function preExecute(SelectQueryInterface $query = NULL) { + // If no query object is passed in, use $this. + if (!isset($query)) { + $query = $this; + } + + // Only execute this once. + if ($query->isPrepared()) { + return TRUE; + } + + // Modules may alter all queries or only those having a particular tag. + if (isset($this->alterTags)) { + $hooks = array('query'); + foreach ($this->alterTags as $tag => $value) { + $hooks[] = 'query_' . $tag; + } + drupal_alter($hooks, $query); + } + + $this->prepared = TRUE; + + // Now also prepare any sub-queries. + foreach ($this->tables as $table) { + if ($table['table'] instanceof SelectQueryInterface) { + $table['table']->preExecute(); + } + } + + foreach ($this->union as $union) { + $union['query']->preExecute(); + } + + return $this->prepared; + } + + public function execute() { + // If validation fails, simply return NULL. + // Note that validation routines in preExecute() may throw exceptions instead. + if (!$this->preExecute()) { + return NULL; + } + + $args = $this->getArguments(); + return $this->connection->query((string) $this, $args, $this->queryOptions); + } + + public function distinct($distinct = TRUE) { + $this->distinct = $distinct; + return $this; + } + + public function addField($table_alias, $field, $alias = NULL) { + // If no alias is specified, first try the field name itself. + if (empty($alias)) { + $alias = $field; + } + + // If that's already in use, try the table name and field name. + if (!empty($this->fields[$alias])) { + $alias = $table_alias . '_' . $field; + } + + // If that is already used, just add a counter until we find an unused alias. + $alias_candidate = $alias; + $count = 2; + while (!empty($this->fields[$alias_candidate])) { + $alias_candidate = $alias . '_' . $count++; + } + $alias = $alias_candidate; + + $this->fields[$alias] = array( + 'field' => $field, + 'table' => $table_alias, + 'alias' => $alias, + ); + + return $alias; + } + + public function fields($table_alias, array $fields = array()) { + + if ($fields) { + foreach ($fields as $field) { + // We don't care what alias was assigned. + $this->addField($table_alias, $field); + } + } + else { + // We want all fields from this table. + $this->tables[$table_alias]['all_fields'] = TRUE; + } + + return $this; + } + + public function addExpression($expression, $alias = NULL, $arguments = array()) { + if (empty($alias)) { + $alias = 'expression'; + } + + $alias_candidate = $alias; + $count = 2; + while (!empty($this->expressions[$alias_candidate])) { + $alias_candidate = $alias . '_' . $count++; + } + $alias = $alias_candidate; + + $this->expressions[$alias] = array( + 'expression' => $expression, + 'alias' => $alias, + 'arguments' => $arguments, + ); + + return $alias; + } + + public function join($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->addJoin('INNER', $table, $alias, $condition, $arguments); + } + + public function innerJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->addJoin('INNER', $table, $alias, $condition, $arguments); + } + + public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->addJoin('LEFT OUTER', $table, $alias, $condition, $arguments); + } + + public function rightJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) { + return $this->addJoin('RIGHT OUTER', $table, $alias, $condition, $arguments); + } + + public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()) { + + if (empty($alias)) { + if ($table instanceof SelectQueryInterface) { + $alias = 'subquery'; + } + else { + $alias = $table; + } + } + + $alias_candidate = $alias; + $count = 2; + while (!empty($this->tables[$alias_candidate])) { + $alias_candidate = $alias . '_' . $count++; + } + $alias = $alias_candidate; + + if (is_string($condition)) { + $condition = str_replace('%alias', $alias, $condition); + } + + $this->tables[$alias] = array( + 'join type' => $type, + 'table' => $table, + 'alias' => $alias, + 'condition' => $condition, + 'arguments' => $arguments, + ); + + return $alias; + } + + public function orderBy($field, $direction = 'ASC') { + $this->order[$field] = $direction; + return $this; + } + + public function orderRandom() { + $alias = $this->addExpression('RAND()', 'random_field'); + $this->orderBy($alias); + return $this; + } + + public function range($start = NULL, $length = NULL) { + $this->range = func_num_args() ? array('start' => $start, 'length' => $length) : array(); + return $this; + } + + public function union(SelectQueryInterface $query, $type = '') { + // Handle UNION aliasing. + switch ($type) { + // Fold UNION DISTINCT to UNION for better cross database support. + case 'DISTINCT': + case '': + $type = 'UNION'; + break; + + case 'ALL': + $type = 'UNION ALL'; + default: + } + + $this->union[] = array( + 'type' => $type, + 'query' => $query, + ); + + return $this; + } + + public function groupBy($field) { + $this->group[$field] = $field; + return $this; + } + + public function countQuery() { + // Create our new query object that we will mutate into a count query. + $count = clone($this); + + $group_by = $count->getGroupBy(); + + if (!$count->distinct) { + // When not executing a distinct query, we can zero-out existing fields + // and expressions that are not used by a GROUP BY. Fields listed in + // the GROUP BY clause need to be present in the query. + $fields =& $count->getFields(); + foreach (array_keys($fields) as $field) { + if (empty($group_by[$field])) { + unset($fields[$field]); + } + } + $expressions =& $count->getExpressions(); + foreach (array_keys($expressions) as $field) { + if (empty($group_by[$field])) { + unset($expressions[$field]); + } + } + + // Also remove 'all_fields' statements, which are expanded into tablename.* + // when the query is executed. + foreach ($count->tables as $alias => &$table) { + unset($table['all_fields']); + } + } + + // If we've just removed all fields from the query, make sure there is at + // least one so that the query still runs. + $count->addExpression('1'); + + // Ordering a count query is a waste of cycles, and breaks on some + // databases anyway. + $orders = &$count->getOrderBy(); + $orders = array(); + + if ($count->distinct && !empty($group_by)) { + // If the query is distinct and contains a GROUP BY, we need to remove the + // distinct because SQL99 does not support counting on distinct multiple fields. + $count->distinct = FALSE; + } + + $query = $this->connection->select($count); + $query->addExpression('COUNT(*)'); + + return $query; + } + + public function __toString() { + // For convenience, we compile the query ourselves if the caller forgot + // to do it. This allows constructs like "(string) $query" to work. When + // the query will be executed, it will be recompiled using the proper + // placeholder generator anyway. + if (!$this->compiled()) { + $this->compile($this->connection, $this); + } + + // Create a sanitized comment string to prepend to the query. + $comments = $this->connection->makeComment($this->comments); + + // SELECT + $query = $comments . 'SELECT '; + if ($this->distinct) { + $query .= 'DISTINCT '; + } + + // FIELDS and EXPRESSIONS + $fields = array(); + foreach ($this->tables as $alias => $table) { + if (!empty($table['all_fields'])) { + $fields[] = $this->connection->escapeTable($alias) . '.*'; + } + } + foreach ($this->fields as $alias => $field) { + // Always use the AS keyword for field aliases, as some + // databases require it (e.g., PostgreSQL). + $fields[] = (isset($field['table']) ? $this->connection->escapeTable($field['table']) . '.' : '') . $this->connection->escapeField($field['field']) . ' AS ' . $this->connection->escapeAlias($field['alias']); + } + foreach ($this->expressions as $alias => $expression) { + $fields[] = $expression['expression'] . ' AS ' . $this->connection->escapeAlias($expression['alias']); + } + $query .= implode(', ', $fields); + + + // FROM - We presume all queries have a FROM, as any query that doesn't won't need the query builder anyway. + $query .= "\nFROM "; + foreach ($this->tables as $alias => $table) { + $query .= "\n"; + if (isset($table['join type'])) { + $query .= $table['join type'] . ' JOIN '; + } + + // If the table is a subquery, compile it and integrate it into this query. + if ($table['table'] instanceof SelectQueryInterface) { + // Run preparation steps on this sub-query before converting to string. + $subquery = $table['table']; + $subquery->preExecute(); + $table_string = '(' . (string) $subquery . ')'; + } + else { + $table_string = '{' . $this->connection->escapeTable($table['table']) . '}'; + } + + // Don't use the AS keyword for table aliases, as some + // databases don't support it (e.g., Oracle). + $query .= $table_string . ' ' . $this->connection->escapeTable($table['alias']); + + if (!empty($table['condition'])) { + $query .= ' ON ' . $table['condition']; + } + } + + // WHERE + if (count($this->where)) { + // There is an implicit string cast on $this->condition. + $query .= "\nWHERE " . $this->where; + } + + // GROUP BY + if ($this->group) { + $query .= "\nGROUP BY " . implode(', ', $this->group); + } + + // HAVING + if (count($this->having)) { + // There is an implicit string cast on $this->having. + $query .= "\nHAVING " . $this->having; + } + + // ORDER BY + if ($this->order) { + $query .= "\nORDER BY "; + $fields = array(); + foreach ($this->order as $field => $direction) { + $fields[] = $field . ' ' . $direction; + } + $query .= implode(', ', $fields); + } + + // RANGE + // There is no universal SQL standard for handling range or limit clauses. + // Fortunately, all core-supported databases use the same range syntax. + // Databases that need a different syntax can override this method and + // do whatever alternate logic they need to. + if (!empty($this->range)) { + $query .= "\nLIMIT " . (int) $this->range['length'] . " OFFSET " . (int) $this->range['start']; + } + + // UNION is a little odd, as the select queries to combine are passed into + // this query, but syntactically they all end up on the same level. + if ($this->union) { + foreach ($this->union as $union) { + $query .= ' ' . $union['type'] . ' ' . (string) $union['query']; + } + } + + if ($this->forUpdate) { + $query .= ' FOR UPDATE'; + } + + return $query; + } + + public function __clone() { + // On cloning, also clone the dependent objects. However, we do not + // want to clone the database connection object as that would duplicate the + // connection itself. + + $this->where = clone($this->where); + $this->having = clone($this->having); + foreach ($this->union as $key => $aggregate) { + $this->union[$key]['query'] = clone($aggregate['query']); + } + } +} + +/** + * @} End of "ingroup database". + */ |