Xataface  2.0alpha2
Xataface Application Framework
 All Data Structures Namespaces Files Functions Variables Groups Pages
QueryTool.php
Go to the documentation of this file.
1 <?php
2 /*-------------------------------------------------------------------------------
3  * Xataface Web Application Framework
4  * Copyright (C) 2005-2008 Web Lite Solutions Corp (shannah@sfu.ca)
5  *
6  * This program is free software; you can redistribute it and/or
7  * modify it under the terms of the GNU General Public License
8  * as published by the Free Software Foundation; either version 2
9  * of the License, or (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program; if not, write to the Free Software
18  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
19  *-------------------------------------------------------------------------------
20  */
21 /*******************************************************************************
22  * File: Dataface/QueryTool.php
23  * Author: Steve Hannah <shannah@sfu.ca>
24  * Created: September 4, 2005
25  * Description:
26  * Encapsulates query results from a table.
27  ******************************************************************************/
28 import( 'Dataface/QueryBuilder.php');
29 import( 'Dataface/Table.php');
30 import( 'Dataface/Record.php');
31 import( 'Dataface/DB.php');
32 
33 
34 $GLOBALS['Dataface_QueryTool_limit'] = 30;
35 $GLOBALS['Dataface_QueryTool_skip'] = 0;
36 class Dataface_QueryTool {
37 
38  var $_table;
39  var $_db;
40  var $_tablename;
41 
42  var $_query;
43 
44  var $_data;
45 
46  var $_currentRecord = null;
47  var $_titles;
48 
49  var $dbObj = null;
50 
51  function &staticCache(){
52  static $cache = 0;
53  if ( $cache === 0 ){
54  $cache = array();
55  }
56  return $cache;
57  }
58 
59 
66  function Dataface_QueryTool($tablename, $db=null, $query=null){
67  $this->dbObj =& Dataface_DB::getInstance();
68  $this->_tablename = $tablename;
69  if ( !is_array($query) ) $query= array();
70  if ( $db === null ){
71  $db = DATAFACE_DB_HANDLE;
72  }
73  $this->_db = $db;
74  $this->_query = $query;
75 
76 
77  $this->_table =& Dataface_Table::loadTable($tablename);
78 
79  $this->_data = array();
80  if ( isset( $query['-cursor'] ) ){
81  $this->_data['cursor'] = $query['-cursor'];
82  } else {
83  $this->_data['cursor'] = 0;
84  }
85 
86  if ( isset( $query['-skip'] ) ){
87  $this->_data['start'] = $query['-skip'];
88  } else {
89  $this->_data['start'] = 0;
90  }
91 
92  if ( isset( $query['-limit'] ) ){
93  $this->_data['end'] = $this->_data['start'] + $query['-limit']-1;
94  $this->_data['limit'] = $query['-limit'];
95  } else {
96  $this->_data['end'] = $this->_data['start'] + $GLOBALS['Dataface_QueryTool_limit']-1;
97  $this->_data['limit'] = $GLOBALS['Dataface_QueryTool_limit'];
98 
99  }
100 
101  $tableKeyNames = array_keys($this->_table->keys());
102  if ( count($tableKeyNames) <= 0 ) throw new Exception("The table '$tablename' has no primary key. Please add one.", E_USER_ERROR);
103 
104  $firstKeyName = $tableKeyNames[0];
105 
106  $cache =& $this->staticCache();
107  $sql = "select count(`$firstKeyName`) from `$tablename`";
108 
109  if ( isset($cache[$sql]) ) $this->_data['cardinality'] = $cache[$sql];
110  else {
111  $res = $this->dbObj->query( $sql, $this->_db,null, true /*as array*/);
112  if ( !$res and !is_array($res) ) throw new Exception("We had a problem with the query $sql.", E_USER_ERROR);
113 
114  $this->_data['cardinality'] = reset($res[0]);
115  $cache[$sql] = $this->_data['cardinality'];
116  }
117 
118  $builder = new Dataface_QueryBuilder( $tablename, $this->_query);
119  $builder->selectMetaData = true;
120  $sql = $builder->select_num_rows();
121  if ( isset($cache[$sql]) ){
122  $this->_data['found'] = $cache[$sql];
123  } else {
124 
125  $res = $this->dbObj->query( $sql, $this->_db,null, true /*as array*/);
126  if ( !$res and !is_array($res) ){
127  throw new Exception(mysql_error($this->_db).$sql, E_USER_ERROR);
128  }
129  $this->_data['found'] = array_shift($res[0]);//mysql_fetch_row( $res );
130  $cache[$sql] = $this->_data['found'];
131  }
132 
133  if ( $this->_data['end'] > $this->_data['found']-1 ){
134  $this->_data['end'] = $this->_data['found']-1;
135  }
136  if ( $this->_data['start'] > $this->_data['found'] ){
137  $this->_data['start'] = $this->_data['found'];
138  }
139 
140 
141  }
142 
143  function getTitles($ordered=true, $genericKeys = false, $ignoreLimit=false){
144  $app =& Dataface_Application::getInstance();
145  if ( !isset($this->_titles[$ordered][$genericKeys][$ignoreLimit]) ){
146  $titleColumn = $this->_table->titleColumn();
147 
148  $keys = array_keys($this->_table->keys());
149  if ( !is_array($keys) || count($keys) == 0 ){
150  throw new Exception(
151  df_translate(
152  'No primary key defined',
153  'There is no primary key defined on table "'.$this->_table->tablename.'". Please define a primary key.',
154  array('table'=>$this->_table->tablename, 'stack_trace'=>'')
155  ),
156  E_USER_ERROR
157  );
158  }
159  $len = strlen($titleColumn);
160  if ( $titleColumn{$len-1} != ')' and $titleColumn{$len-1} != '`') $titleColumn = '`'.$titleColumn.'`';
161 
162  $builder = new Dataface_QueryBuilder( $this->_tablename, $this->_query);
163  $builder->action = 'select';
164  $from = $builder->_from();
165  $sql = "SELECT `".implode('`,`',$keys)."`,$titleColumn as `__titleColumn__` $from";
166  $where = $builder->_where();
167  $where = $builder->_secure($where);
168  $limit = $builder->_limit();
169  if ( strlen($where)>0 ){
170  $sql .= " $where";
171  }
172  if ( $ordered ){
173  $sql .= " ORDER BY `__titleColumn__`";
174  } else {
175  $sql .= $builder->_orderby();
176  }
177  if ( strlen($limit)>0 and !$ignoreLimit ){
178  $sql .= " $limit";
179  } else if ( !$ignoreLimit) {
180  $sql .= " LIMIT 250";
181  }
182  $res = $this->dbObj->query($sql, $this->_table->db, null,true /* as array */);
183  if ( !$res and !is_array($res) ){
184  $app->refreshSchemas($this->_table->tablename);
185  // updates meta tables such as workflow tables to make sure that they
186  // are up to date.
187  $res = $this->dbObj->query($sql, $this->_table->db,null, true /* as array */);
188  if ( !$res and !is_array($res) )
189  throw new Exception(
190  df_translate(
191  'scripts.Dataface.QueryTool.getTitles.ERROR_ERROR_RETRIEVING_TITLES',
192  "Error retrieving title from database in Dataface_QueryTool::getTitles(): "
193  )
194  .$sql.mysql_error($this->_table->db), E_USER_ERROR);
195  }
196  $titles = array();
197  //while ( $row = mysql_fetch_row($res) ){
198  foreach ( $res as $row ){
199  $title = array_pop($row);
200  if ( !$genericKeys) {
201  $keyvals = array();
202  reset($keys);
203  while ( sizeof($row)>0 ){
204  $keyvals[current($keys)] = array_shift($row);
205  next($keys);
206  }
207 
208  $keystr = '';
209  foreach ($keyvals as $keykey=>$keyval){
210  $keystr .= urlencode($keykey)."=".urlencode($keyval)."&";
211  }
212  $keystr = substr($keystr, 0, strlen($keystr)-1);
213  $titles[$keystr] = $title;
214  } else {
215  $titles[] = $title;
216  }
217 
218  }
219  //@mysql_free_result($res);
220 
221  $this->_titles[$ordered][$genericKeys][$ignoreLimit] =& $titles;
222  }
223 
224  return $this->_titles[$ordered][$genericKeys][$ignoreLimit];
225  }
226 
235  function loadSet($columns='', $loadText=false, $loadBlobs=false, $preview=true){
236  $app =& Dataface_Application::getInstance();
237  //It turns out that QueryBuilder handles whether or not blobs should be loaded so we won't worry about that here.
238  $loadText=true;
239  $loadBlobs=true;
240 
241  $fields = $this->_table->fields(false, true);
242  $fieldnames = array_keys($fields);
243  $builder = new Dataface_QueryBuilder($this->_tablename, $this->_query);
244  $builder->selectMetaData = true;
245  // We set selectMetaData true so that the field lengths will be loaded as well.
246  // This is especially useful for blob fields, since we don't load blobs - but
247  // we still want to know th size of the blob.
248 
249  // initialize the loaded mask
250  if ( !isset( $this->_data['loaded'] ) ){
251  $this->_data['loaded'] = array();
252  foreach ($fieldnames as $fieldname){
253  $this->_data['loaded'][$fieldname] = false;
254  }
255  }
256  $loaded =& $this->_data['loaded'];
257 
258  // figure out which columns still need to be loaded.
259  $cols = array();
260  if ( is_array($columns) ){
261  $cols = $columns;
262  } else {
263  foreach ($fieldnames as $col){
264  if ( $loaded[$col] ) continue;
265  $cols[] = $col;
266  }
267  }
268 
269  if ( sizeof( $cols ) > 0 ){
270  // we need to load a couple of columns
271  $tablekeys = array_keys($this->_table->keys());
272  $select_cols = array_merge($cols, $tablekeys);
273  $sql = $builder->select($select_cols, array(), false, null, $preview);
274 
275 
276  $res = $this->dbObj->query( $sql, $this->_db, null, true/*as array*/);
277  if ( !$res and !is_array($res) ){
278  $app->refreshSchemas($this->_table->tablename);
279  $res = $this->dbObj->query( $sql, $this->_db, null, true/*as array*/);
280  if ( !$res and !is_array($res) )
281 
282  throw new Exception(
283  df_translate(
284  'scripts.Dataface.QueryTool.loadSet.ERROR_LOADING_RECORDS',
285  "Error loading records in Dataface_QueryTool::loadSet(): "
286  )
287  .mysql_error($this->_db)."\n<br>".$sql, E_USER_ERROR);
288  }
289  if ( !isset( $this->_data['start'] ) )
290  $this->_data['start'] = $this->_query['-skip'];
291  if ( !isset( $this->_data['end'] ) )
292  $this->_data['end'] = $this->_query['-skip'] + count($res);//mysql_num_rows($res);
293 
294  if ( !isset( $this->_data['data'] ) ){
295  $this->_data['data'] = array();
296  $this->_data['indexedData'] = array();
297  }
298 
299  $fieldnames = array_keys( $this->_table->fields(false, true) );
300 
301  foreach ( $res as $row){
302  $key='';
303  foreach ($tablekeys as $name){
304  $key .= $row[$name];
305  }
306 
307  foreach ($row as $att=>$attval){
308  if ( !in_array($att, $fieldnames) and strpos($att,'__')!== 0 ){
309  unset( $row[$att] );
310  }
311  }
312 
313  if ( !isset( $this->_data['data'][$key] ) ){
314  $this->_data['data'][$key] = $row;
315  $this->_data['indexedData'][] =& $this->_data['data'][$key];
316  } else {
317  foreach ($cols as $col){
318  $this->_data['data'][$key][$col] = $row[$col];
319  }
320  }
321  }
322  //@mysql_free_result($res);
323 
324  foreach ($cols as $col){
325  $loaded[$col] = true;
326  }
327 
328 
329  }
330  $cache =& $this->staticCache();
331  if (!isset( $this->_data['found'] ) ){
332 
333  $sql = $builder->select_num_rows();
334 
335  if ( isset($cache[$sql]) ){
336  $this->_data['found'] = $cache[$sql];
337  } else {
338  $res = $this->dbObj->query( $sql, $this->_db,null, true /*as array*/);
339  $this->_data['found'] = array_shift($res[0]);
340  $cache[$sql] = $this->_data['found'];
341  }
342 
343  }
344 
345  if ( !isset( $this->_data['cardinality'] ) ){
346  $tableKeyNames = array_keys($this->_table->keys());
347  if ( count($tableKeyNames) <= 0 ) throw new Exception("The table '$tablename' has no primary key. Please add one.", E_USER_ERROR);
348 
349  $firstKeyName = $tableKeyNames[0];
350  $sql = "select count(`$firstKeyName`) from `".$this->_tablename.'`';
351 
352  if ( isset($cache[$sql]) ) $this->_data['cardinality'] = $cache[$sql];
353  else {
354  $res = $this->dbObj->query( $sql, $this->_db,null, true /*as array*/);
355  $this->_data['cardinality'] = array_shift($res[0]);
356  $cache[$sql] = $this->_data['cardinality'];
357  }
358 
359 
360  }
361 
362  return true;
363 
364 
365  }
366 
367  function &loadCurrent($columns=null, $loadText=true, $loadBlobs=false, $loadPasswords=false){
368  $app =& Dataface_Application::getInstance();
369  $false = false; // boolean placeholders for values needing to be returned by reference
370  $true = true;
371 
372  if ( $this->_currentRecord === null ){
373  //require_once 'Dataface/IO.php';
374  //$io = new Dataface_IO($this->_table->tablename);
375  //$query = array_merge( $this->_query, array('-skip'=>$this->_data['cursor'], '-limit'=>1) );
376  $this->_currentRecord = new Dataface_Record($this->_table->tablename, array());
377  //$io->read($query, $this->_currentRecord);
378 
379  }
380  //return $this->_currentRecord;
381 
382 
383  $unloaded = array();
384  $fields =& $this->_table->fields(false, true);
385  if ( $columns === null ) {
386  $names = array_keys($fields);
387  } else {
388  $names = $columns;
389  }
390 
391  foreach ($names as $name){
392  if ( !$this->_currentRecord->isLoaded($name) ){
393  if ( !$loadText and $this->_table->isText($name) ) continue;
394  if ( !$loadBlobs and $this->_table->isBlob($name) ) continue;
395  if ( !$loadPasswords and $this->_table->isPassword($name) ) continue;
396  $unloaded[] = $name;
397  }
398  }
399 
400  if ( sizeof( $unloaded ) > 0 ){
401 
402  $query = array_merge( $this->_query, array('-skip'=>$this->_data['cursor'], '-limit'=>1) );
403  $builder = new Dataface_QueryBuilder( $this->_tablename, $query);
404  $builder->selectMetaData = true;
405  $builder->_omitBlobs = false;
406 
407  $sql = $builder->select($unloaded);
408  //echo $sql;
409  if ( PEAR::isError($sql) ){
410  throw new Exception($sql->toString(), E_USER_ERROR);
411  }
412 
413  //echo $sql;
414  $res = $this->dbObj->query($sql, $this->_db,null, true /* as array */);
415  if ( !$res and !is_array($res) ){
416  $app->refreshSchemas($this->_table->tablename);
417  $res = $this->dbObj->query($sql, $this->_db, null,true /* as array */);
418  if ( !$res and !is_array($res) ){
419  error_log(df_translate('scripts.Dataface.QueryTool.loadCurrent.ERROR_COULD_NOT_LOAD_CURRENT_RECORD',"Error: Could not load current record: ").mysql_error( $this->_db)."\n$sql");
420  throw new Exception("Failed to load current record due to an SQL error");
421 
422  }
423  }
424  if (count($res) <= 0 ){
425  return $false;
426  }
427  $row = $res[0]; //mysql_fetch_assoc($res);
428  //@mysql_free_result($row);
429  $this->_currentRecord = new Dataface_Record($this->_table->tablename, $row);
430  //$this->_table->setValues($row);
431  //$this->_table->setSnapshot();
432  //$this->_table->deserialize();
433  }
434 
435  return $this->_currentRecord;
436 
437 
438  }
439 
440 
441  function found(){
442  if (!isset( $this->_data['found'] ) ){
443  $cache =& $this->staticCache();
444  $builder = new Dataface_QueryBuilder($this->_tablename,$this->_query);
445  $sql = $builder->select_num_rows();
446  if ( isset($cache[$sql]) ) $this->_data['found'] = $cache[$sql];
447  else {
448  $res = $this->dbObj->query( $sql, $this->_db, null,true /*as array*/);
449  $this->_data['found'] = array_shift($res[0]);
450  $cache[$sql] = $this->_data['found'];
451  }
452 
453  }
454  return $this->_data['found'];
455 
456  }
457 
458 
459  function cardinality(){
460  if ( !isset( $this->_data['cardinality'] ) ){
461  $cache =& $this->staticCache();
462  $tableKeyNames = array_keys($this->_table->keys());
463  if ( count($tableKeyNames) <= 0 ) throw new Exception("The table '$tablename' has no primary key. Please add one.", E_USER_ERROR);
464 
465  $firstKeyName = $tableKeyNames[0];
466  $sql = "select count(`$firstKeyName`) from ".$this->_tablename;
467  if ( isset($cache[$sql]) ) $this->_data['cardinality'] = $cache[$sql];
468  else {
469  $res = $this->dbObj->query( $sql, $this->_db,null, true /*as array*/);
470  $this->_data['cardinality'] = array_shift($res[0]);
471  $cache[$sql] = $this->_data['cardinality'];
472  }
473 
474 
475  }
476  return $this->_data['cardinality'];
477  }
478 
479  function start(){
480  return $this->_data['start'];
481  }
482  function end(){
483  return $this->_data['end'];
484  }
485 
486  function &data(){
487  return $this->_data['data'];
488  }
489 
490  function &iterator(){
491  $it = new Dataface_RecordIterator($this->_tablename, $this->data());
492  return $it;
493  }
494 
495  function getRecordsArray(){
496 
497  $records = array();
498  $it = $this->iterator();
499  if ( PEAR::isError($it) )return $it;
500  while ($it->hasNext()){
501  $records[] = $it->next();
502  }
503  return $records;
504  }
505 
506  function limit(){
507  return $this->_data['limit'];
508  }
509 
510 
511  function cursor(){
512  return $this->_data['cursor'];
513  }
514 
515  function &indexedData(){
516  return $this->_data['indexedData'];
517  }
518 
519  public static function &loadResult($tablename, $db=null, $query=''){
520  if ( $db === null and defined('DATAFACE_DB_HANDLE') ) $db = DATAFACE_DB_HANDLE;
521  if ( !isset( $resultDescriptors ) ){
522  static $resultDescriptors = array();
523  }
524 
525  if ( is_array($query) and @$query['--no-query'] ){
526  $out = new Dataface_QueryTool_Null($tablename, $db, $query);
527  return $out;
528  }
529 
530  if ( !isset( $resultDescriptors[$tablename] ) ){
531  $resultDescriptors[$tablename] = new Dataface_QueryTool($tablename, $db , $query);
532  }
533  return $resultDescriptors[$tablename];
534  }
535 
536 }
537 
538 
539 class Dataface_QueryTool_Null extends Dataface_QueryTool {
540 
541  function &staticCache(){
542  static $cache = 0;
543  if ( $cache === 0 ){
544  $cache = array();
545  }
546  return $cache;
547  }
548 
549 
556  function __construct($tablename, $db=null, $query=null){
557 
558 
559 
560  }
561 
562  function getTitles($ordered=true, $genericKeys = false, $ignoreLimit=false){
563  return array();
564  }
565 
574  function loadSet($columns='', $loadText=false, $loadBlobs=false, $preview=true){
575  return true;
576 
577 
578  }
579 
580  function &loadCurrent($columns=null, $loadText=true, $loadBlobs=false, $loadPasswords=false){
581  return null;
582 
583 
584  }
585 
586 
587  function found(){
588  return 0;
589 
590  }
591 
592 
593  function cardinality(){
594  return 0;
595  }
596 
597  function start(){
598  return 0;
599  }
600  function end(){
601  return 0;
602  }
603 
604  function &data(){
605  return array();
606  }
607 
608  function &iterator(){
609  $it = new Dataface_RecordIterator($this->_tablename, $this->data());
610  return $it;
611  }
612 
613  function getRecordsArray(){
614  return array();
615  }
616 
617  function limit(){
618  return 0;
619  }
620 
621 
622  function cursor(){
623  return 0;
624  }
625 
626  function &indexedData(){
627  return array();
628  }
629 
630 
631 }
Generated on Fri Mar 22 2013 09:40:34 for Xataface by   doxygen 1.8.1.2