Fakoli Framework
query.inc
Go to the documentation of this file.
1 <?php
2 /**************************************************************
3 
4  Copyright (c) 2007-2010 Sonjara, Inc
5 
6  Permission is hereby granted, free of charge, to any person
7  obtaining a copy of this software and associated documentation
8  files (the "Software"), to deal in the Software without
9  restriction, including without limitation the rights to use,
10  copy, modify, merge, publish, distribute, sublicense, and/or sell
11  copies of the Software, and to permit persons to whom the
12  Software is furnished to do so, subject to the following
13  conditions:
14 
15  The above copyright notice and this permission notice shall be
16  included in all copies or substantial portions of the Software.
17 
18  Except as contained in this notice, the name(s) of the above
19  copyright holders shall not be used in advertising or otherwise
20  to promote the sale, use or other dealings in this Software
21  without prior written authorization.
22 
23  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
24  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
25  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
26  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
27  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
28  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
29  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
30  OTHER DEALINGS IN THE SOFTWARE.
31 
32 *****************************************************************/
33 
34 abstract class AbstractQuery
35 {
36  var $class;
38  var $filter;
39  var $params;
40 
41  public function __construct($class, $constraints = "")
42  {
43  $this->class = $class;
44  $this->constraints = $constraints;
45 
46  $this->filter = null;
47  $this->params = array();
48  }
49 
55  function constraints($constraints)
56  {
57  $this->constraints = $constraints;
58  return $this;
59  }
60 
66  function filter($filter)
67  {
68  $this->filter = $filter;
69  return $this;
70  }
71 
78  function params($params)
79  {
80  $this->params = $params;
81  return $this;
82  }
83 
88  function bind()
89  {
90  $num = func_num_args();
91  for($i = 0; $i < $num; $i += 2)
92  {
93  $this->params[func_get_arg($i)] = func_get_arg($i + 1);
94  }
95 
96  return $this;
97  }
98 
99  abstract function execute();
100 }
101 
112 class Query extends AbstractQuery
113 {
114  var $page;
115  var $size;
117 
124  public function __construct($class, $constraints = "")
125  {
126  parent::__construct($class, $constraints);
127 
128  $this->page = -1;
129  $this->size = -1;
130 
131  $this->tableAlias = "";
132  }
133 
134 
142  function page($page, $size)
143  {
144  $this->page = $page;
145  $this->size = $size;
146  return $this;
147  }
148 
149 
158  function tableAlias($alias)
159  {
160  $this->tableAlias = $alias;
161  return $this;
162  }
163 
167  function exists()
168  {
169  $prototype = new $this->class;
170  $pk = $prototype->getPrimaryKey();
171 
172  $query = "SELECT $pk FROM {$prototype->table} {$this->tableAlias} {$this->constraints} LIMIT 1";
173 
174  $exists = false;
175 
176  try
177  {
179 
180  $result = $db->prepare($query);
181  $result->execute($this->params);
182 
183  if ($result->fetch()) $exists = true;
184 
185  unset($result);
186  }
187  catch(PDOException $e)
188  {
189  throw new DataItemException($e->getMessage());
190  }
191 
192  return $exists;
193 
194  }
195 
201  function execute()
202  {
203  $prototype = new $this->class;
204  $prototype->filter = $this->filter;
205 
206  $order_by_idx = strpos(strtoupper($this->constraints), "ORDER BY");
207  $orderBy = "";
208 
209  if ($order_by_idx !== false)
210  {
211  $orderBy = substr($this->constraints, $order_by_idx);
212 
213  $this->constraints = substr($this->constraints, 0, $order_by_idx);
214  }
215 
216  if ($this->constraints == "") $this->constraints = "WHERE 1=1"; //TODO - tidy this up some day
217  $this->constraints .= " ".$prototype->getIdentityConstraint();
218 
219  $query = "SELECT ".$prototype->getFieldList()." FROM {$prototype->table} {$this->tableAlias} {$this->constraints} $orderBy";
220 
221  trace("$query", 3);
222  trace("Page: $this->page Size: $this->size", 3);
223  $items = array();
224 
225  $size = $this->size;
226 
227  try
228  {
230 
231  $result = $db->prepare($query);
232  $result->execute($this->params);
233 
234  if ($this->page > 0)
235  {
236  $count = ($this->page - 1) * $this->size;
237  while($count--)
238  {
239  $result->fetch();
240  }
241  }
242 
243  while($line = $result->fetch())
244  {
245  $item = new $this->class;
246  $item->filter = $this->filter;
247  $item->populate($line);
248  $items[] = $item;
249 
250  --$size;
251  if ($size == 0) break;
252  }
253 
254  unset($result);
255  }
256  catch(PDOException $e)
257  {
258  $err = "query() failed - " . $e->getMessage();
259  trace($err, 2);
260  throw new DataItemException($err);
261  }
262 
263  trace(count($items)." items found", 3);
264  return $items;
265  }
266 
273  function executeSingle()
274  {
275  $results = $this->execute();
276  if (count($results) == 1)
277  {
278  return $results[0];
279  }
280 
281  if (count($results) == 0)
282  {
283  throw new DataNotFoundException();
284  }
285  else
286  {
287  throw new DataItemException("Ambiguous singleton query");
288  }
289  }
290 
296  function executeValue($func)
297  {
298  $prototype = new $this->class;
299 
300  if ($this->constraints == "") $this->constraints = "WHERE 1=1"; //TODO - tidy this up some day
301  $this->constraints .= " ".$prototype->getIdentityConstraint();
302 
303  $query = "SELECT $func as result FROM {$prototype->table} {$this->tableAlias} {$this->constraints}";
304 
305  trace($query, 3);
306 
307  try
308  {
310 
311  $result = $db->prepare($query);
312  $result->execute($this->params);
313 
314  if ($row = $result->fetch())
315  {
316  $value = $row['result'];
317  }
318 
319  unset($result);
320  }
321  catch(PDOException $e)
322  {
323  $err = "Query::executeValue() failed - " . $e->getMessage();
324  trace($err, 2);
325  throw new DataItemException($err);
326  }
327 
328  return $value;
329  }
330 
334  function count()
335  {
336  return $this->executeValue("COUNT(1)");
337  }
338 
345  static function create($class, $constraints = "")
346  {
347  return new Query($class, $constraints);
348  }
349 }
350 
360 function query($class)
361 {
362  $query = new Query($class);
363 
364  if (func_num_args() > 1)
365  {
366  $idx = 1;
367 
368  if (is_object(func_get_arg(1)))
369  {
370  $query->filter($filter);
371  ++$idx;
372  }
373 
374  if (is_array(func_get_arg($idx)))
375  {
376  $query->params(func_get_arg($idx++));
377  }
378 
379  if (func_num_args() > $idx)
380  {
381  $query->constraints(func_get_arg($idx));
382 
383  if (func_num_args() == $idx + 3)
384  {
385  $query->page(func_get_arg($idx + 1), func_get_arg($idx + 2));
386  }
387  }
388  }
389 
390  return $query->execute();
391 }
392 
393 ?>
execute()
Executes the query and returns an array of DataItems containing the results (the class of items retur...
Definition: query.inc:201
Query provides an object-oriented interface for database queries.
Definition: query.inc:112
page($page, $size)
Sets the page number and size for constraining the result set by page.
Definition: query.inc:142
params($params)
Sets the bound parameters array.
Definition: query.inc:78
exists()
Return true if the query matches one or more rows in the database.
Definition: query.inc:167
trace($msg, $lvl, $callStack=null)
Send output to the trace log.
Definition: functions.inc:959
constraints($constraints)
Sets the constraint clause for the Query.
Definition: query.inc:55
bind()
Binds placeholders to parameter values.
Definition: query.inc:88
__construct($class, $constraints="")
Definition: query.inc:41
$size
Definition: query.inc:115
filter($filter)
Sets a filter to constrain the fields retrieved when the query is executed.
Definition: query.inc:66
__construct($class, $constraints="")
Creates a new Query object.
Definition: query.inc:124
$page
Definition: query.inc:114
executeSingle()
Executes the query and returns a singleton result row.
Definition: query.inc:273
tableAlias($alias)
Sometimes when you are creating abonimable SQL constraints you might need to refer back to the main o...
Definition: query.inc:158
static getConnection()
Retrieves a reference to the global database connection.
count()
Return the count of matching results.
Definition: query.inc:334
query($class)
Performs a query against the database, returning an array of DataItem objects of the specified class...
Definition: query.inc:360
$tableAlias
Definition: query.inc:116
executeValue($func)
Query the database to calculate an aggregate value.
Definition: query.inc:296
static create($class, $constraints="")
Static factory method to create a new Query.
Definition: query.inc:345