From cf6d4c680bcd81319282c87fd8561461884d4691 Mon Sep 17 00:00:00 2001 From: icewing Date: Wed, 4 Jun 2008 09:06:30 +0000 Subject: Marcus Povey * Moved Query object et al to their own file git-svn-id: https://code.elgg.org/elgg/trunk@785 36083f99-b078-4883-b0ff-0f9b5a30f544 --- engine/lib/database.php | 743 ----------------------------------------------- engine/lib/query.php | 757 ++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 757 insertions(+), 743 deletions(-) create mode 100644 engine/lib/query.php (limited to 'engine/lib') diff --git a/engine/lib/database.php b/engine/lib/database.php index aea310841..55d2533c2 100644 --- a/engine/lib/database.php +++ b/engine/lib/database.php @@ -11,749 +11,6 @@ * @copyright Curverider Ltd 2008 * @link http://elgg.org/ */ - - /** - * @class QueryComponent Query component superclass. - * Component of a query. - * @author Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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 Marcus Povey - */ - 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("Where set contains non WhereQueryComponent"); - - if (!$cnt) - $string.= $where->toStringNoLink(); - else - $string.=" $where "; - - $cnt ++; - } - $string .= ")"; - - return $string; - } - } - - /** - * @class QueryTypeQueryComponent - * What type of query is this? - * @author Marcus Povey - */ - abstract class QueryTypeQueryComponent extends QueryComponent - { - function __toString() - { - return $this->query_type; - } - } - - /** - * @class SelectQueryTypeQueryComponent - * A select query. - * @author Marcus Povey - */ - class SelectQueryTypeQueryComponent extends QueryTypeQueryComponent - { - function __construct() - { - $this->query_type = "select"; - } - } - - /** - * @class InsertQueryTypeQueryComponent - * An insert query. - * @author Marcus Povey - */ - class InsertQueryTypeQueryComponent extends QueryTypeQueryComponent - { - function __construct() - { - $this->query_type = "insert into"; - } - } - - /** - * @class DeleteQueryTypeQueryComponent - * A delete query. - * @author Marcus Povey - */ - class DeleteQueryTypeQueryComponent extends QueryTypeQueryComponent - { - function __construct() - { - $this->query_type = "delete from"; - } - } - - /** - * @class UpdateQueryTypeQueryComponent - * An update query. - * @author Marcus Povey - */ - class UpdateQueryTypeQueryComponent extends QueryTypeQueryComponent - { - function __construct() - { - $this->query_type = "update"; - } - } - - /** - * @class Query - * This class provides a framework to construct complex queries in a safe environment. - * - * @author Marcus Povey - */ - 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()); - } - - public function setLimitAndOffset(LimitOffsetQueryComponent $component) { $this->limit_and_offset = $component; } - - public function setSelectField(SelectFieldQueryComponent $component) - { - $this->fields = array(); - return $this->addSelectField($component); - } - - public function addSelectField(SelectFieldQueryComponent $component) { $this->fields[] = $component; } - - public function addJoin(JoinQueryComponent $component) { $this->joins[] = $component; } - - public function addSet(SetQueryComponent $component) { $this->sets[] = $component; } - - public function setQueryType(QueryTypeQueryComponent $component) { $this->query_type = $component; } - - public function setOrder(OrderQueryComponent $component) { $this->order = $component; } - - public function addTable(TableQueryComponent $component) { $this->tables[] = $component; } - - public function addWhere(WhereQueryComponent $component) { $this->where[] = $component; } - - 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("Fields missing on a select style query"); - } - else - throw new DatabaseException("Unrecognised or unspecified query type."); - - // Tables - if (!empty($this->tables)) - { - foreach($this->tables as $table) - $sql .= "$table, "; - - $sql = trim($sql, ", "); - } - else - throw new DatabaseException("No tables specified for query."); - - // 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("No access control was provided on query"); - - // 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. - * @author Marcus Povey - */ - 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; - } - } /** * Connect to the database server and use the Elgg database for a particular database link diff --git a/engine/lib/query.php b/engine/lib/query.php new file mode 100644 index 000000000..8b6df5c8b --- /dev/null +++ b/engine/lib/query.php @@ -0,0 +1,757 @@ +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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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 Marcus Povey + */ + 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("Where set contains non WhereQueryComponent"); + + if (!$cnt) + $string.= $where->toStringNoLink(); + else + $string.=" $where "; + + $cnt ++; + } + $string .= ")"; + + return $string; + } + } + + /** + * @class QueryTypeQueryComponent + * What type of query is this? + * @author Marcus Povey + */ + abstract class QueryTypeQueryComponent extends QueryComponent + { + function __toString() + { + return $this->query_type; + } + } + + /** + * @class SelectQueryTypeQueryComponent + * A select query. + * @author Marcus Povey + */ + class SelectQueryTypeQueryComponent extends QueryTypeQueryComponent + { + function __construct() + { + $this->query_type = "select"; + } + } + + /** + * @class InsertQueryTypeQueryComponent + * An insert query. + * @author Marcus Povey + */ + class InsertQueryTypeQueryComponent extends QueryTypeQueryComponent + { + function __construct() + { + $this->query_type = "insert into"; + } + } + + /** + * @class DeleteQueryTypeQueryComponent + * A delete query. + * @author Marcus Povey + */ + class DeleteQueryTypeQueryComponent extends QueryTypeQueryComponent + { + function __construct() + { + $this->query_type = "delete from"; + } + } + + /** + * @class UpdateQueryTypeQueryComponent + * An update query. + * @author Marcus Povey + */ + class UpdateQueryTypeQueryComponent extends QueryTypeQueryComponent + { + function __construct() + { + $this->query_type = "update"; + } + } + + /** + * @class Query + * This class provides a framework to construct complex queries in a safe environment. + * + * @author Marcus Povey + */ + 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()); + } + + public function setLimitAndOffset(LimitOffsetQueryComponent $component) { $this->limit_and_offset = $component; } + + public function setSelectField(SelectFieldQueryComponent $component) + { + $this->fields = array(); + return $this->addSelectField($component); + } + + public function addSelectField(SelectFieldQueryComponent $component) { $this->fields[] = $component; } + + public function addJoin(JoinQueryComponent $component) { $this->joins[] = $component; } + + public function addSet(SetQueryComponent $component) { $this->sets[] = $component; } + + public function setQueryType(QueryTypeQueryComponent $component) { $this->query_type = $component; } + + public function setOrder(OrderQueryComponent $component) { $this->order = $component; } + + public function addTable(TableQueryComponent $component) { $this->tables[] = $component; } + + public function addWhere(WhereQueryComponent $component) { $this->where[] = $component; } + + 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("Fields missing on a select style query"); + } + else + throw new DatabaseException("Unrecognised or unspecified query type."); + + // Tables + if (!empty($this->tables)) + { + foreach($this->tables as $table) + $sql .= "$table, "; + + $sql = trim($sql, ", "); + } + else + throw new DatabaseException("No tables specified for query."); + + // 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("No access control was provided on query"); + + // 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. + * @author Marcus Povey + */ + 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; + } + } +?> \ No newline at end of file -- cgit v1.2.3