Marcus Povey <marcus@dushka.co.uk>

* 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

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))