<?php /** * Elgg database query * Contains a wrapper for performing database queries in a structured way. * * @package Elgg * @subpackage Core * @license http://www.gnu.org/licenses/old-licenses/gpl-2.0.html GNU Public License version 2 * @author Curverider Ltd * @copyright Curverider Ltd 2008-2009 * @link http://elgg.org/ */ /** * @class QueryComponent Query component superclass. * Component of a query. * @author Curverider Ltd * @see Query */ abstract class QueryComponent { /** * Associative array of fields and values */ private $fields; function __construct() { $this->fields = array(); } /** * Class member get overloading * * @param string $name * @return mixed */ function __get($name) { return $this->fields[$name]; } /** * Class member set overloading * * @param string $name * @param mixed $value * @return void */ 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 { /** * Construct a select field component * * @param string $table The table containing the field. * @param string $field The field or "*" */ function __construct($table, $field) { global $CONFIG; $this->table = $CONFIG->dbprefix . sanitise_string($table); $this->field = sanitise_string($field); } function __toString() { return "{$this->table}.{$this->field}"; } } /** * @class LimitOffsetQueryComponent * Limit and offset clauses of a query. * @author Curverider Ltd * @see Query */ class LimitOffsetQueryComponent extends QueryComponent { /** * Specify a limit and an offset. * * @param int $limit The limit. * @param int $offset The offset. */ function __construct($limit = 25, $offset = 0) { $this->limit = (int)$limit; $this->offset = (int)$offset; } function __toString() { return "limit {$this->offset}, {$this->limit}"; } } /** * @class OrderQueryComponent * Order the query results. * @author Curverider Ltd * @see Query */ class OrderQueryComponent extends QueryComponent { 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. * @author Curverider Ltd * @see Query */ class TableQueryComponent extends QueryComponent { function __construct($table) { global $CONFIG; $this->table = $CONFIG->dbprefix . sanitise_string($table); } function __toString() { return $this->table; } } /** * @class AccessControlQueryComponent * Access control component. * @author Curverider Ltd * @see Query */ class AccessControlQueryComponent extends QueryComponent { /** * 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. * @param string $object_owner_id_field The field in $object_owner_table containing the owner information */ 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(); 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. * @author Curverider Ltd * @see Query */ class JoinQueryComponent extends QueryComponent { /** * Construct a join query. * @param string $table Table one to join... * @param string $field Field 1 with... * @param string $table2 Table 2 ... * @param string $field2 Field... * @param string $operator Using this 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() { return "join {$this->table2} on {$this->$table}.{$this->$field} {$this->$operator} {$this->$table2}.{$this->$field2}"; } } /** * @class SetQueryComponent Set query. * Represents an update set query. * @author Curverider Ltd * @see Query */ class SetQueryComponent extends QueryComponent { /** * Construct a setting query * * @param string $table The table to modify * @param string $field The field to modify * @param mixed $value The value to set it to */ 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)."'"; } function __toString() { return "{$this->table}.{$this->field}={$this->value}"; } } /** * @class WhereQueryComponent * A component of a where query. * @author Curverider Ltd * @see Query */ class WhereQueryComponent extends QueryComponent { /** * 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 "<" * @param string $right_table The table on the right of the operator * @param string $right_field The right field * @param string $link_operator How this where clause links with the previous clause, eg. "and" "or" */ 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); } /** * Return the SQL without the link operator. */ public function toStringNoLink() { return "{$this->left_table }.{$this->left_field} {$this->operator} {$this->right_table}.{$this->right_field}"; } 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. * @author Curverider Ltd * @see Query */ class WhereStaticQueryComponent extends WhereQueryComponent { /** * 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 "<" * @param string $value The value * @param string $link_operator How this where clause links with the previous clause, eg. "and" "or" */ 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)."'"; } /** * Return the SQL without the link operator. */ public function toStringNoLink() { return "{$this->left_table }.{$this->left_field} {$this->operator} {$this->value}"; } } /** * @class WhereSetQueryComponent * A where query that may contain other where queries (in brackets). * @author Curverider Ltd * @see Query */ class WhereSetQueryComponent extends WhereQueryComponent { /** * Construct a subset of wheres. * * @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") { $this->link_operator = sanitise_string($link_operator); $this->wheres = $wheres; } public function toStringNoLink() { $cnt = 0; $string = " ("; 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 ++; } $string .= ")"; return $string; } } /** * @class QueryTypeQueryComponent * What type of query is this? * @author Curverider Ltd * @see Query */ abstract class QueryTypeQueryComponent extends QueryComponent { function __toString() { return $this->query_type; } } /** * @class SelectQueryTypeQueryComponent * A select query. * @author Curverider Ltd * @see Query */ class SelectQueryTypeQueryComponent extends QueryTypeQueryComponent { function __construct() { $this->query_type = "SELECT"; } } /** * @class InsertQueryTypeQueryComponent * An insert query. * @author Curverider Ltd * @see Query */ class InsertQueryTypeQueryComponent extends QueryTypeQueryComponent { function __construct() { $this->query_type = "INSERT INTO"; } } /** * @class DeleteQueryTypeQueryComponent * A delete query. * @author Curverider Ltd * @see Query */ class DeleteQueryTypeQueryComponent extends QueryTypeQueryComponent { function __construct() { $this->query_type = "DELETE FROM"; } } /** * @class UpdateQueryTypeQueryComponent * An update query. * @author Curverider Ltd * @see Query */ class UpdateQueryTypeQueryComponent extends QueryTypeQueryComponent { function __construct() { $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 * 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 * 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 * 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 * the log instead. * * 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. * $query->addWhere( * new WhereSetQueryComponent( * array( * new WhereStaticQueryComponent("entities", "subtype","=", 1), * new WhereQueryComponent("entities","subtype","=", "entities", "subtype") * ) * ) * ); * * get_data($query); * </blockquote> * * @author Curverider Ltd */ class Query { /// The limit of the query 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 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() { $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) { $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"; $sql .= " $fields from "; } else throw new DatabaseException(elgg_echo('DatabaseException:SelectFieldsMissing')); } else throw new DatabaseException(elgg_echo('DatabaseException:UnspecifiedQueryType')); // Tables if (!empty($this->tables)) { 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 (!empty($this->joins)) { foreach($this->joins as $join) $sql .= "$join "; } } // Setting values if ( ($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)) { // 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 * 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() { 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()); break; case "delete" : return $this->setQueryType(DeleteQueryTypeQueryComponent()); break; case "update" : return $this->setQueryType(UpdateQueryTypeQueryComponent()); break; default: return $this->setQueryType(SelectQueryTypeQueryComponent()); } } /** * 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. */ 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 $field2 Field... * @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 "<" * @param string $right_table The table on the right of the operator * @param string $right_field The right field * @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 "<" * @param string $value The value * @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. */ public function simpleAccessControl($acl_table = "entities", $acl_field = "access_id", $object_owner_id_field = "owner_guid") { return $this->setAccessControl(new AccessControlQueryComponent($acl_table, $acl_field, $acl_table, $object_owner_id_field)); } /** * 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") { $table = sanitise_string($table); $field = sanitise_string($field); $order = strtolower(sanitise_string($order)); return $this->setOrder(new OrderQueryComponent($table, $field, $order)); break; } } ?>