aboutsummaryrefslogtreecommitdiff
path: root/engine
diff options
context:
space:
mode:
Diffstat (limited to 'engine')
-rw-r--r--engine/lib/database.php520
1 files 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))
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 04:02:18 +0000