From 8292d46d158494122784cfdc9e6ae9b077540276 Mon Sep 17 00:00:00 2001 From: icewing Date: Wed, 28 May 2008 10:52:57 +0000 Subject: Marcus Povey * Draft Query object and companion classes committed for comment. [UNTESTED] git-svn-id: https://code.elgg.org/elgg/trunk@731 36083f99-b078-4883-b0ff-0f9b5a30f544 --- engine/lib/database.php | 520 +++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 509 insertions(+), 11 deletions(-) diff --git a/engine/lib/database.php b/engine/lib/database.php index fef31e682..6b8db1956 100644 --- a/engine/lib/database.php +++ b/engine/lib/database.php @@ -11,7 +11,505 @@ * @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->prefix . 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->prefix . 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) + { + $this->table = sanitise_string($table); + } + } + + 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 + */ + 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->prefix . sanitise_string($acl_table); + $this->acl_field = sanitise_string($acl_field); + $this->object_owner_table = $CONFIG->prefix . 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->prefix . sanitise_string($table1); + $this->field1 = sanitise_string($field1); + $this->table2 = $CONFIG->prefix . 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 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->prefix . sanitise_string($left_table); + $this->left_field = sanitise_string($left_field); + $this->operator = sanitise_string($operator); + $this->right_table = $CONFIG->prefix . sanitise_string($right_table); + $this->right_field = sanitise_string($right_field); + } + + function __toString() + { + return "{$this->link_operator} {$this->left_table }.{$this->left_field} {$this->operator} {$this->right_table}.{$this->right_field}"; + } + } + + /** + * @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->prefix . 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); + } + + function __toString() + { + return "{$this->link_operator} {$this->left_table }.{$this->left_field} {$this->operator} {$this->right_table}.{$this->right_field}"; + } + } + + 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; + } + + function __toString() + { + $cnt = 0; + $string = "{$this->link_operator} ("; + foreach ($this->wheres as $where) { + if (!($where instanceof WhereQueryComponent)) + throw new DatabaseException("Where set contains non WhereQueryComponent"); + + if (!$cnt) + $string .= "{$where->link_operator} "; + + $string .= " {$where->left_table }.{$where->left_field} {$where->operator} {$where->right_table}.{$where->right_field}"; + $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; + + /// 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->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 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 = ""; + + // 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("Unrecognised or unspecified query type."); + + // Tables + if (!empty($this->tables)) + { + foreach($this->tables as $table) + $sql .= "$table, "; + + $sql = trim($sql, ", "); + } + + // Joins on select queries + if ($this->query_type->query_type == 'select') + { + if (!empty($this->joins)) + { + foreach($this->joins as $join) + $sql .= "$join "; + } + } + + // Where + if (!empty($this->where)) + { + $sql .= "where 1 "; + + foreach ($this->where as $where) + $sql .= "$where "; + } + + // Access control + if (!empty($this->access_control)) + $sql .= "{$this->access_control} "; + else + throw DatabaseException("No access control was provided on query"); + + // Limits + if (!empty($this->limit_and_offset)) + $sql .= "{$this->limit_and_offset} "; + + // Order by + if (!empty($this->order)) + $sql .= $this->order; + + + return $sql; + } + + } + /** * Connect to the database server and use the Elgg database for a particular database link * @@ -120,7 +618,7 @@ /** * Use this function to get data from the database - * @param string $query The query being passed. + * @param mixed $query The query being passed. * @param string $call Optionally, the name of a function to call back to on each row (which takes $row as a single parameter) * @return array An array of database result objects */ @@ -140,7 +638,7 @@ error_log("--- EXPLAINATION --- " . print_r(explain_query($query,$dblink), true)); } - if ($result = mysql_query($query, $dblink)) { + if ($result = mysql_query("$query", $dblink)) { while ($row = mysql_fetch_object($result)) { if (!empty($callback) && is_callable($callback)) { $row = $callback($row); @@ -163,7 +661,7 @@ /** * Use this function to get a single data row from the database - * @param $query The query to run. + * @param mixed $query The query to run. * @return object A single database result object */ @@ -181,7 +679,7 @@ error_log("--- EXPLAINATION --- " . print_r(explain_query($query,$dblink), true)); } - if ($result = mysql_query($query, $dblink)) { + if ($result = mysql_query("$query", $dblink)) { while ($row = mysql_fetch_object($result)) { return $row; } @@ -199,7 +697,7 @@ /** * Use this function to insert database data; returns id or false * - * @param string $query The query to run. + * @param mixed $query The query to run. * @return int $id the database id of the inserted row. */ @@ -211,7 +709,7 @@ $dbcalls++; - if (mysql_query($query, $dblink)) + if (mysql_query("$query", $dblink)) return mysql_insert_id($dblink); if (mysql_errno($dblink)) @@ -223,7 +721,7 @@ /** * Update database data * - * @param string $query The query to run. + * @param mixed $query The query to run. * @return int|false Either the number of affected rows, or false on failure */ @@ -235,7 +733,7 @@ $dbcalls++; - if (mysql_query($query, $dblink)) + if (mysql_query("$query", $dblink)) return mysql_affected_rows(); if (mysql_errno($dblink)) @@ -248,7 +746,7 @@ /** * Use this function to delete data * - * @param string $query The SQL query to run + * @param mixed $query The SQL query to run * @return int|false Either the number of affected rows, or false on failure */ @@ -260,7 +758,7 @@ $dbcalls++; - if (mysql_query($query, $dblink)) + if (mysql_query("$query", $dblink)) return mysql_affected_rows(); if (mysql_errno($dblink)) -- cgit v1.2.3