aboutsummaryrefslogtreecommitdiff
path: root/engine/classes/Query.php
blob: ebc7e1474c3d75142c3bb7ac0800d29561f80124 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
<?php
/**
 * @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;
	}

}