diff options
Diffstat (limited to 'engine/lib/query.php')
| -rw-r--r-- | engine/lib/query.php | 449 |
1 files changed, 224 insertions, 225 deletions
diff --git a/engine/lib/query.php b/engine/lib/query.php index 75b9eddeb..619a2d288 100644 --- a/engine/lib/query.php +++ b/engine/lib/query.php @@ -2,7 +2,7 @@ /** * Elgg database query * Contains a wrapper for performing database queries in a structured way. - * + * * @package Elgg * @subpackage Core * @author Curverider Ltd @@ -16,18 +16,18 @@ * @author Curverider Ltd * @see Query */ - abstract class QueryComponent + abstract class QueryComponent { /** * Associative array of fields and values - */ + */ private $fields; function __construct() { - $this->fields = array(); + $this->fields = array(); } - + /** * Class member get overloading * @@ -37,7 +37,7 @@ function __get($name) { return $this->fields[$name]; } - + /** * Class member set overloading * @@ -47,39 +47,39 @@ */ function __set($name, $value) { $this->fields[$name] = $value; - + return true; - } + } } - + /** * @class SelectFieldQueryComponent Class representing a select field. * This class represents a select field component. * @author Curverider Ltd * @see Query */ - class SelectFieldQueryComponent extends QueryComponent + class SelectFieldQueryComponent extends QueryComponent { /** * Construct a select field component - * + * * @param string $table The table containing the field. * @param string $field The field or "*" */ - function __construct($table, $field) + function __construct($table, $field) { global $CONFIG; - + $this->table = $CONFIG->dbprefix . sanitise_string($table); $this->field = sanitise_string($field); } - - function __toString() + + function __toString() { return "{$this->table}.{$this->field}"; } } - + /** * @class LimitOffsetQueryComponent * Limit and offset clauses of a query. @@ -90,7 +90,7 @@ { /** * Specify a limit and an offset. - * + * * @param int $limit The limit. * @param int $offset The offset. */ @@ -99,13 +99,13 @@ $this->limit = (int)$limit; $this->offset = (int)$offset; } - + function __toString() { return "limit {$this->offset}, {$this->limit}"; } } - + /** * @class OrderQueryComponent * Order the query results. @@ -117,18 +117,18 @@ function __construct($table, $field, $order = "asc") { global $CONFIG; - + $this->table = $CONFIG->dbprefix . sanitise_string($table); $this->field = sanitise_string($field); $this->order = sanitise_string($order); } - + function __toString() { return "order by {$this->table}.{$this->field} {$this->order}"; } } - + /** * @class TableQueryComponent * List of tables to select from or insert into. @@ -140,16 +140,16 @@ function __construct($table) { global $CONFIG; - + $this->table = $CONFIG->dbprefix . sanitise_string($table); } - + function __toString() { return $this->table; } } - + /** * @class AccessControlQueryComponent * Access control component. @@ -160,7 +160,7 @@ { /** * Construct the ACL. - * + * * @param string $acl_table The table where the access control field is. * @param string $acl_field The field containing the access control. * @param string $object_owner_table The table containing the owner information for the stuff you're retrieving. @@ -169,13 +169,13 @@ function __construct($acl_table = "entities", $acl_field = "access_id", $object_owner_table = "entities", $object_owner_id_field = "owner_guid") { global $CONFIG; - + $this->acl_table = $CONFIG->dbprefix . sanitise_string($acl_table); $this->acl_field = sanitise_string($acl_field); $this->object_owner_table = $CONFIG->dbprefix . sanitise_string($object_owner_table); $this->object_owner_id_field = sanitise_string($object_owner_id_field); } - + function __toString() { //$access = get_access_list(); @@ -185,11 +185,11 @@ // $object_owner_id_field = "owner_guid" // TODO: recode get_access_sql_suffix to make it possible to specify alternate field names return "and ".get_access_sql_suffix($this->acl_table); // Add access controls - + //return "and ({$this->acl_table}.{$this->acl_field} in {$access} or ({$this->acl_table}.{$this->acl_field} = 0 and {$this->object_owner_table}.{$this->object_owner_id_field} = {$_SESSION['id']}))"; } } - + /** * @class JoinQueryComponent Join query. * Represents a join query. @@ -202,27 +202,27 @@ * Construct a join query. * @param string $table Table one to join... * @param string $field Field 1 with... - * @param string $table2 Table 2 ... + * @param string $table2 Table 2 ... * @param string $field2 Field... * @param string $operator Using this operator */ - function __construct($table1, $field1, $table2, $field2, $operator = "=") + function __construct($table1, $field1, $table2, $field2, $operator = "=") { global $CONFIG; - + $this->table1 = $CONFIG->dbprefix . sanitise_string($table1); $this->field1 = sanitise_string($field1); $this->table2 = $CONFIG->dbprefix . sanitise_string($table2); $this->field2 = sanitise_string($field2); $this->operator = sanitise_string($operator); } - - function __toString() + + function __toString() { return "join {$this->table2} on {$this->$table}.{$this->$field} {$this->$operator} {$this->$table2}.{$this->$field2}"; } } - + /** * @class SetQueryComponent Set query. * Represents an update set query. @@ -241,21 +241,21 @@ function __construct($table, $field, $value) { global $CONFIG; - - $this->table = $CONFIG->dbprefix . sanitise_string($table); - $this->field = sanitise_string($field); - if (is_numeric($value)) - $this->value = (int)$value; - else - $this->value = "'".sanitise_string($value)."'"; + + $this->table = $CONFIG->dbprefix . sanitise_string($table); + $this->field = sanitise_string($field); + if (is_numeric($value)) + $this->value = (int)$value; + else + $this->value = "'".sanitise_string($value)."'"; } - - function __toString() + + function __toString() { return "{$this->table}.{$this->field}={$this->value}"; } } - + /** * @class WhereQueryComponent * A component of a where query. @@ -266,7 +266,7 @@ { /** * A where query. - * + * * @param string $left_table The table on the left of the operator * @param string $left_field The left field * @param string $operator The operator eg "=" or "<" @@ -277,15 +277,15 @@ function __construct($left_table, $left_field, $operator, $right_table, $right_field, $link_operator = "and") { global $CONFIG; - + $this->link_operator = sanitise_string($link_operator); - $this->left_table = $CONFIG->dbprefix . sanitise_string($left_table); - $this->left_field = sanitise_string($left_field); - $this->operator = sanitise_string($operator); - $this->right_table = $CONFIG->dbprefix . sanitise_string($right_table); - $this->right_field = sanitise_string($right_field); + $this->left_table = $CONFIG->dbprefix . sanitise_string($left_table); + $this->left_field = sanitise_string($left_field); + $this->operator = sanitise_string($operator); + $this->right_table = $CONFIG->dbprefix . sanitise_string($right_table); + $this->right_field = sanitise_string($right_field); } - + /** * Return the SQL without the link operator. */ @@ -293,13 +293,13 @@ { return "{$this->left_table }.{$this->left_field} {$this->operator} {$this->right_table}.{$this->right_field}"; } - - function __toString() + + function __toString() { return "{$this->link_operator} " . $this->toStringNoLink(); } } - + /** * @class WhereStaticQueryComponent * A component of a where query where there is no right hand table, rather a static value. @@ -310,7 +310,7 @@ { /** * A where query. - * + * * @param string $left_table The table on the left of the operator * @param string $left_field The left field * @param string $operator The operator eg "=" or "<" @@ -320,17 +320,17 @@ function __construct($left_table, $left_field, $operator, $value, $link_operator = "and") { global $CONFIG; - + $this->link_operator = sanitise_string($link_operator); - $this->left_table = $CONFIG->dbprefix . sanitise_string($left_table); - $this->left_field = sanitise_string($left_field); - $this->operator = sanitise_string($operator); - if (is_numeric($value)) - $this->value = (int)$value; - else - $this->value = "'".sanitise_string($value)."'"; + $this->left_table = $CONFIG->dbprefix . sanitise_string($left_table); + $this->left_field = sanitise_string($left_field); + $this->operator = sanitise_string($operator); + if (is_numeric($value)) + $this->value = (int)$value; + else + $this->value = "'".sanitise_string($value)."'"; } - + /** * Return the SQL without the link operator. */ @@ -339,7 +339,7 @@ return "{$this->left_table }.{$this->left_field} {$this->operator} {$this->value}"; } } - + /** * @class WhereSetQueryComponent * A where query that may contain other where queries (in brackets). @@ -351,7 +351,7 @@ /** * Construct a subset of wheres. * - * @param array $wheres An array of WhereQueryComponent + * @param array $wheres An array of WhereQueryComponent * @param string $link_operator How this where clause links with the previous clause, eg. "and" "or" */ function __construct(array $wheres, $link_operator = "and") @@ -359,7 +359,7 @@ $this->link_operator = sanitise_string($link_operator); $this->wheres = $wheres; } - + public function toStringNoLink() { $cnt = 0; @@ -367,135 +367,135 @@ foreach ($this->wheres as $where) { if (!($where instanceof WhereQueryComponent)) throw new DatabaseException(elgg_echo('DatabaseException:WhereSetNonQuery')); - + if (!$cnt) $string.= $where->toStringNoLink(); else $string.=" $where "; - - $cnt ++; + + $cnt ++; } $string .= ")"; - + return $string; } } - + /** * @class QueryTypeQueryComponent * What type of query is this? * @author Curverider Ltd * @see Query */ - abstract class QueryTypeQueryComponent extends QueryComponent + abstract class QueryTypeQueryComponent extends QueryComponent { function __toString() { - return $this->query_type; + return $this->query_type; } } - + /** * @class SelectQueryTypeQueryComponent * A select query. * @author Curverider Ltd * @see Query */ - class SelectQueryTypeQueryComponent extends QueryTypeQueryComponent + class SelectQueryTypeQueryComponent extends QueryTypeQueryComponent { - function __construct() + function __construct() { - $this->query_type = "SELECT"; + $this->query_type = "SELECT"; } } - + /** * @class InsertQueryTypeQueryComponent * An insert query. * @author Curverider Ltd * @see Query */ - class InsertQueryTypeQueryComponent extends QueryTypeQueryComponent + class InsertQueryTypeQueryComponent extends QueryTypeQueryComponent { - function __construct() + function __construct() { - $this->query_type = "INSERT INTO"; + $this->query_type = "INSERT INTO"; } } - + /** * @class DeleteQueryTypeQueryComponent * A delete query. * @author Curverider Ltd * @see Query */ - class DeleteQueryTypeQueryComponent extends QueryTypeQueryComponent + class DeleteQueryTypeQueryComponent extends QueryTypeQueryComponent { - function __construct() + function __construct() { - $this->query_type = "DELETE FROM"; + $this->query_type = "DELETE FROM"; } } - + /** * @class UpdateQueryTypeQueryComponent * An update query. * @author Curverider Ltd * @see Query */ - class UpdateQueryTypeQueryComponent extends QueryTypeQueryComponent + class UpdateQueryTypeQueryComponent extends QueryTypeQueryComponent { - function __construct() + function __construct() { - $this->query_type = "UPDATE"; + $this->query_type = "UPDATE"; } } - + /** * @class Query Provides a framework to construct complex queries in a safer environment. - * + * * The usage of this class depends on the type of query you are executing, but the basic idea is to * construct a query out of pluggable classes. - * - * Once constructed SQL can be generated using the toString method, this should happen automatically + * + * Once constructed SQL can be generated using the toString method, this should happen automatically * if you pass the Query object to get_data or similar. - * - * To construct a query, create a new Query() object and begin populating it with the various classes + * + * To construct a query, create a new Query() object and begin populating it with the various classes * that define the various aspects of the query. - * + * * Notes: - * - You do not have to specify things in any particular order, provided you specify all required + * - You do not have to specify things in any particular order, provided you specify all required * components. * - With database tables you do not have to specify your db prefix, this will be added automatically. - * - When constructing your query keep an eye on the error log - any problems will get spit out here. - * Note also that __toString won't let you throw Exceptions (!!!) so these are caught and echoed to + * - When constructing your query keep an eye on the error log - any problems will get spit out here. + * Note also that __toString won't let you throw Exceptions (!!!) so these are caught and echoed to * the log instead. - * - * Here is an example of a select query which requests some data out of the entities table with an + * + * Here is an example of a select query which requests some data out of the entities table with an * order and limit that uses a subset where and some normal where queries: - * + * * <blockquote> * // Construct the query * $query = new Query(); - * + * * // Say which table we're interested in * $query->addTable(new TableQueryComponent("entities")); - * + * * // What fields are we interested in * $query->addSelectField(new SelectFieldQueryComponent("entities","*")); - * + * * // Add access control (Default access control uses default fields on entities table. * // Note that it will error without something specified here! * $query->setAccessControl(new AccessControlQueryComponent()); - * + * * // Set a limit and offset, may be omitted. * $query->setLimitAndOffset(new LimitOffsetQueryComponent(10,0)); - * + * * // Specify the order, may be omitted * $query->setOrder(new OrderQueryComponent("entities", "subtype", "desc")); - * + * * // Construct a where query - * // + * // * // This demonstrates a WhereSet which lets you have sub wheres, a * // WhereStatic which lets you compare a table field against a value and a * // Where which lets you compare a table/field with another table/field. @@ -507,150 +507,150 @@ * ) * ) * ); - * + * * get_data($query); * </blockquote> - * + * * @author Curverider Ltd */ class Query { - + /// The limit of the query - private $limit_and_offset; - + private $limit_and_offset; + /// Fields to return on a query private $fields; - + /// Tables to use in a from query private $tables; - + /// Join tables private $joins; - - /// Set values + + /// Set values private $sets; - + /// Where query private $where; - + /// Order by private $order; - + /// The query type private $query_type; - + /// ACL private $access_control; - + /** * Construct query & initialise variables */ - function __construct() + function __construct() { $this->fields = array(); $this->tables = array(); $this->joins = array(); $this->where = array(); $this->sets = array(); - + $this->setQueryType(new SelectQueryTypeQueryComponent()); } - + /** * Add limits and offsets to the query. - * + * * @param LimitOffsetQueryComponent $component The limit and offset. */ public function setLimitAndOffset(LimitOffsetQueryComponent $component) { $this->limit_and_offset = $component; } - + /** * Reset and set the field to the select statement. - * + * * @param SelectFieldQueryComponent $component Table and field component. */ - public function setSelectField(SelectFieldQueryComponent $component) + public function setSelectField(SelectFieldQueryComponent $component) { $this->fields = array(); return $this->addSelectField($component); } - + /** * Add a select field. - * + * * @param SelectFieldQueryComponent $component Add a component. */ public function addSelectField(SelectFieldQueryComponent $component) { $this->fields[] = $component; } - + /** * Add a join to the component. - * + * * @param JoinQueryComponent $component The join. */ public function addJoin(JoinQueryComponent $component) { $this->joins[] = $component; } - + /** * Set a field value in an update or insert statement. - * + * * @param SetQueryComponent $component Fields to set. */ public function addSet(SetQueryComponent $component) { $this->sets[] = $component; } - + /** * Set the query type, i.e. "select", "update", "insert" & "delete". - * + * * @param QueryTypeQueryComponent $component The query type. */ public function setQueryType(QueryTypeQueryComponent $component) { $this->query_type = $component; } - + /** * Attach an order component. - * + * * @param OrderQueryComponent $component The order component. */ public function setOrder(OrderQueryComponent $component) { $this->order = $component; } - + /** * Add a table to the query. - * + * * @param TableQueryComponent $component Table to add. */ public function addTable(TableQueryComponent $component) { $this->tables[] = $component; } - + /** * Add a where clause to the query. - * + * * @param WhereQueryComponent $component The where component */ public function addWhere(WhereQueryComponent $component) { $this->where[] = $component; } - + /** * Set access control. - * + * * @param AccessControlQueryComponent $component Access control. */ public function setAccessControl(AccessControlQueryComponent $component) { $this->access_control = $component; } - + public function __toString() { global $CONFIG; - + $sql = ""; - + try - { + { // Query prefix & fields if (!empty($this->query_type)) { $sql .= "{$this->query_type} "; - + if (!empty($this->fields)) { $fields = ""; - + foreach ($this->fields as $field) - $fields .= "$field"; - + $fields .= "$field"; + $sql .= " $fields from "; } else @@ -658,131 +658,131 @@ } else throw new DatabaseException(elgg_echo('DatabaseException:UnspecifiedQueryType')); - + // Tables - if (!empty($this->tables)) + if (!empty($this->tables)) { - foreach($this->tables as $table) + foreach($this->tables as $table) $sql .= "$table, "; - + $sql = trim($sql, ", "); } else throw new DatabaseException(elgg_echo('DatabaseException:NoTablesSpecified')); - + // Joins on select queries - if ($this->query_type->query_type == 'select') + if ($this->query_type->query_type == 'select') { - if (!empty($this->joins)) - { - foreach($this->joins as $join) + if (!empty($this->joins)) + { + foreach($this->joins as $join) $sql .= "$join "; } } - + // Setting values if ( - ($this->query_type->query_type == 'update') || + ($this->query_type->query_type == 'update') || ($this->query_type->query_type == 'insert') - ) + ) { $sql .= "set "; - + foreach ($this->sets as $set) $sql .= "$set, "; - + $sql = trim($sql, ", ") . " "; } - + // Where if (!empty($this->where)) { $sql .= " where 1 "; - + foreach ($this->where as $where) $sql .= "$where "; } - + // Access control - if (!empty($this->access_control)) + if (!empty($this->access_control)) { - + // Catch missing Where if (empty($this->where)) $sql .= " where 1 "; - + $sql .= "{$this->access_control} "; - } + } else throw new DatabaseException(elgg_echo('DatabaseException:NoACL')); - + // Order by if (!empty($this->order)) $sql .= "{$this->order} "; - + // Limits if (!empty($this->limit_and_offset)) $sql .= "{$this->limit_and_offset} "; - - - + + + } catch (Exception $e) { trigger_error($e, E_USER_WARNING); } - - + + return $sql; } - + } - + /** * @class SimpleQuery A wrapper for Query which provides simple interface for common functions. - * - * This class provides simple interface functions for constructing a (reasonably) standard database + * + * This class provides simple interface functions for constructing a (reasonably) standard database * query. - * + * * The constructor for this class sets a number of defaults, for example sets default access controls * and a limit and offset - to change this then set it manually. - * + * * @author Curverider Ltd * @see Query */ class SimpleQuery extends Query { - function __construct() + function __construct() { parent::__construct(); - + // Set a default query type (select) $this->simpleQueryType(); - + // Set a default access control $this->simpleAccessControl(); - + // Set default limit and offset $this->simpleLimitAndOffset(); } - + /** * Set the query type. - * + * * @param string $type The type of search - available are "select", "update", "delete", "insert". */ public function simpleQueryType($type = "select") { $type = strtolower(sanitise_string($type)); - + switch ($type) { case "insert" : - return $this->setQueryType(InsertQueryTypeQueryComponent()); + return $this->setQueryType(InsertQueryTypeQueryComponent()); break; - case "delete" : + case "delete" : return $this->setQueryType(DeleteQueryTypeQueryComponent()); break; case "update" : - return $this->setQueryType(UpdateQueryTypeQueryComponent()); + return $this->setQueryType(UpdateQueryTypeQueryComponent()); break; default: return $this->setQueryType(SelectQueryTypeQueryComponent()); } @@ -790,50 +790,50 @@ /** * Set a field to query in a select statement. - * + * * @param string $table Table to query. * @param string $field Field in that table. */ public function simpleSelectField($table, $field) { return $this->setSelectField(new SelectFieldQueryComponent($table, $field)); } - + /** * Add a select field to query in a select statement. - * + * * @param string $table Table to query. - * @param string $field Field in that table. + * @param string $field Field in that table. */ public function simpleAddSelectField($table, $field) { return $this->addSelectField(new SelectFieldQueryComponent($table, $field)); } - + /** * Add a set value to an update query. - * + * * @param string $table The table to update. * @param string $field The field in the table. * @param mixed $value The value to set it to. */ public function simpleSet($table, $field, $value) { return $this->addSet(new SetQueryComponent($table, $field, $value)); } - + /** * Add a join to the table. - * + * * @param string $table Table one to join... * @param string $field Field 1 with... - * @param string $table2 Table 2 ... + * @param string $table2 Table 2 ... * @param string $field2 Field... - * @param string $operator Using this operator + * @param string $operator Using this operator */ public function simpleJoin($table1, $field1, $table2, $field2, $operator = "=") { return $this->addJoin(new JoinQueryComponent($table1, $field1, $table2, $field2, $operator)); } - + /** * Add a table to the query. - * + * * @param string $table The table. */ public function simpleTable($table) { return $this->addTable(new TableQueryComponent($table)); } /** * Compare one table/field to another table/field. - * + * * @param string $left_table The table on the left of the operator * @param string $left_field The left field * @param string $operator The operator eg "=" or "<" @@ -842,10 +842,10 @@ * @param string $link_operator How this where clause links with the previous clause, eg. "and" "or" */ public function simpleWhereOnTable($left_table, $left_field, $operator, $right_table, $right_field, $link_operator = "and") { return $this->addWhere(new WhereQueryComponent($left_table, $left_field, $operator, $right_table, $right_field, $link_operator)); } - + /** * Compare one table/field to a value. - * + * * @param string $left_table The table on the left of the operator * @param string $left_field The left field * @param string $operator The operator eg "=" or "<" @@ -853,10 +853,10 @@ * @param string $link_operator How this where clause links with the previous clause, eg. "and" "or" */ public function simpleWhereOnValue($left_table, $left_field, $operator, $value, $link_operator = "and") { return $this->addWhere(new WhereStaticQueryComponent($left_table, $left_field, $operator, $value, $link_operator)); } - + /** * Set access control. - * + * * @param string $acl_table The table where the access control field is. * @param string $acl_field The field containing the access control. * @param string $object_owner_id_field The field in $object_owner_table containing the owner information. @@ -865,26 +865,25 @@ /** * Set the limit and offset. - * + * * @param int $limit The limit. * @param int $offset The offset. */ public function simpleLimitAndOffset($limit = 25, $offset = 0) { return $this->setLimitAndOffset(new LimitOffsetQueryComponent($limit, $offset)); } - + /** * Set the order query. - * + * * @param string $table The table to query * @param string $field The field to query * @param string $order Order the query */ - public function simpleOrder($table, $field, $order = "desc") + public function simpleOrder($table, $field, $order = "desc") { $table = sanitise_string($table); $field = sanitise_string($field); $order = strtolower(sanitise_string($order)); - + return $this->setOrder(new OrderQueryComponent($table, $field, $order)); break; } } -?>
\ No newline at end of file |