Xataface  2.0alpha2
Xataface Application Framework
 All Data Structures Namespaces Files Functions Variables Groups Pages
QueryBuilder.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 /*******************************************************************************
23  * File: Dataface/QueryBuilder.php
24  * Author: Steve Hannah <shannah@sfu.ca>
25  * Created: Sept. 2, 2005
26  * Description:
27  * Builds SQL queries based on key-value pair queries.
28  *
29  ******************************************************************************/
30 
31 import( 'PEAR.php');
32 import( 'Dataface/Table.php');
33 import( 'Dataface/Error.php');
34 import( 'Dataface/Serializer.php');
35 import('Dataface/DB.php'); // for Blob registry.
36 
37 define('QUERYBUILDER_ERROR_EMPTY_SELECT', 1);
38 
39 class Dataface_QueryBuilder {
40 
50  var $_query;
51 
55  var $_tablename;
56 
60  var $_table;
61 
65  var $_fields;
66 
67  var $_mutableFields;
68 
72  var $_exactMatches = false;
73 
77  var $_omitBlobs = true;
78 
82  var $_omitPasswords = true;
83 
90  var $_security = array();
91 
92 
93  var $_serializer;
94 
95  var $errors = array();
96 
97  /*
98  * Whether or not meta data should be selected along with select statements.
99  * Meta data includes calculations of the "lengths" of the data in particular
100  * fields. This is particularly handy for blob fields where we are interested
101  * in the size of the blob.
102  */
103  var $selectMetaData = false;
104 
108  var $metadata = false;
109 
110 
117  var $action = null;
118 
119 
120 
121 
122 
130  function Dataface_QueryBuilder($tablename, $query=''){
131  $this->_tablename = $tablename;
132  $this->_table =& Dataface_Table::loadTable($tablename);
133  $this->_query = is_array($query) ? $query : array();
134  $this->_fields =& $this->_table->fields(false, true);
135  $this->_mutableFields =& $this->_table->fields();
136  $this->_serializer = new Dataface_Serializer($tablename);
137  $this->action = null;
138 
139  $app =& Dataface_Application::getInstance();
140  if ( @$app->_conf['metadata_enabled'] ){
141  $this->metadata = true;
142  }
143 
144  $keys = array_keys( $this->_query );
145  foreach ($keys as $key){
146  if ( $this->_query[$key] === ''){
147  unset( $this->_query[$key] );
148  }
149  }
150 
151 
152  if ( isset( $GLOBALS['DATAFACE_QUERYBUILDER_SECURITY_CONSTRAINTS'][$tablename]) ){
153  $this->_security = $GLOBALS['DATAFACE_QUERYBUILDER_SECURITY_CONSTRAINTS'][$tablename];
154  }
155 
156 
157  }
158 
159  function _opt($code, $isText=true){
160  switch ( $code ){
161  case '=':
162  case '>':
163  case '<':
164  case '>=':
165  case '<=':
166  return $code;
167  default:
168  return 'LIKE';
169  }
170  }
171 
172 
173 
192  function search($queryStr, $columns='', $queryParams=array(), $nolimit=false){
193  $this->action='search';
194  $ret = $this->_select($columns);
195  $from = trim($this->_from($this->_table->tablename));
196  $indexOnly = ( (isset($queryParams['-ignoreIndex']) and $queryParams['-useIndex']) ? false : true );
197  $where = "WHERE ".$this->_match($queryStr, $indexOnly);
198  $from = trim( $this->_from($this->_table->tablename));
199  $order = trim($this->_orderby($queryParams));
200  $limit = trim($this->_limit($queryParams, $nolimit));
201  if ( strlen($from)>0 ) $ret .= ' '.$from;
202  if ( strlen($where)>0 ) $ret .= ' '.$where;
203  if ( strlen($order)>0 ) $ret .= ' '.$order;
204  if ( strlen($limit)>0 ) $ret .= ' '.$limit;
205  $this->action = null;
206  return $ret;
207 
208 
209  }
215  function select($columns='', $query=array(), $nolimit=false, $tablename=null, $preview=false){
216  $this->action="select";
217  if ( !is_array($query) ){
218  $query = array();
219  }
220  $query = array_merge( $this->_query, $query);
221 
222  $ret = $this->_select($columns, array(), $preview);
223  if ( PEAR::isError($ret) ){
224  $ret->addUserInfo("Failed to select columns in select() ");
225 
226  return $ret;
227  }
228  $from = trim($this->_from($tablename));
229  $where = trim($this->_where($query));
230  $where = $this->_secure($where);
231  //$having = $this->_having($query);
232 
233  $order = trim($this->_orderby($query));
234  $limit = trim($this->_limit($query, $nolimit));
235 
236  if ( strlen($from)>0 ) $ret .= ' '.$from;
237  if ( strlen($where)>0 ) $ret .= ' '.$where;
238  //if ( strlen($having)>0 ) $ret .= ' '.$having;
239  if ( strlen($order)>0 ) $ret .= ' '.$order;
240  if ( strlen($limit)>0 ) $ret .= ' '.$limit;
241  $this->action = null;
242  //echo $ret;
243  return $ret;
244  }
245 
252  function select_num_rows($query=array(), $tablename=null){
253 
254  $this->action='select_num_rows';
255  $query = array_merge( $this->_query, $query);
256  $ret = 'SELECT COUNT(*) as num';
257  $from = $this->_from($this->tablename($tablename));
258  $where = $this->_where($query);
259  $where = $this->_secure($where);
260 
261  if ( strlen($from)>0 ) $ret .= ' '.$from;
262  if ( strlen($where)>0 ) $ret .= ' '.$where;
263  $this->action = null;
264  return trim($ret);
265 
266 
267  }
268 
269 
276  function update(&$record, $key_vals=null, $tablename=null){
277  $app =& Dataface_Application::getInstance();
278  $this->action='update';
279  // Step 1: Make sure that the input is valid
280  if ( !is_a($record, "Dataface_Record") ){
281  throw new Exception("Attempt to use QueryBuilder::update() where something other than a Dataface_Record object is defined.", E_USER_ERROR);
282  }
283 
284  // Step 2: Start building the sql query. We use an array to build up the query, but
285  // string concatenation would work just as well. We switched to arrays in an attempt to
286  // fix a bug related to updating BLOB fields, but it turned out that the problem was different.
287  $tableObj =& Dataface_Table::loadTable($this->tablename($tablename));
288  $dbObj =& Dataface_DB::getInstance();
289  $sql = array();
290  $sql[] = "UPDATE `".$this->tablename($tablename)."` SET ";
291  $fieldnames = array_keys($this->_mutableFields);
292  // Get all of the field names in this table.
293  $keys = array_keys($this->_table->keys());
294  // Get the names of the key fields in this table
295  $changed = false;
296  // A flag to indicate whether the record has been changed.
297  foreach ($fieldnames as $fieldname){
298  if ( isset($fieldArr) ) unset($fieldArr);
299  $fieldArr =& $tableObj->getField($fieldname);
300  if ( @$fieldArr['ignore'] ) continue;
301  if ( !$record->valueChanged($fieldname) and !(isset($fieldArr['timestamp']) and strtolower($fieldArr['timestamp']) == 'update') ) {
302  // If this field has not been changed then we don't need to update it.
303  // Note that in order for valueChanged() to work properly, the Dataface_Record::setSnapshot()
304  // method must be called at somepoint to indicate that that snapshot represents the last unchanged
305  // state of the record.
306  continue;
307  }
308  if ( $tableObj->isVersioned() and $tableObj->getVersionField() === $fieldname ){
309  continue;
310  }
311  //echo "$fieldname changed\n";
312 
313  // If we made it this far, then the current field has indeed changed
314  $changed = true;
315 
316  $sval = $this->_serializer->serialize($fieldname, $record->getValue($fieldname));
317  // Serialize the field's value to prepare it for the database
318  if ( !isset($sval) and @$fieldArr['timestamp'] != 'update' ){
319  $sql[] = "`$fieldname` = NULL, ";
320  } else if ( $tableObj->isBlob($fieldname) and @$app->_conf['multilingual_content']){
321  // This is a blob column... we don't place the data directly in the String because it would take
322  // too long to parse when Dataface_DB needs to parse it.
323  // Instead we register the BLOB and store its id number.
324  $blobID = $dbObj->registerBlob($sval);
325  $sql[] = "`$fieldname` = '-=-=B".$blobID."=-=-', ";
326  } else if ( $tableObj->isDate($fieldname) and
327  isset($fieldArr['timestamp']) and
328  strtolower($fieldArr['timestamp']) == 'update'){
329  $sql[] = "`$fieldname` = NOW(), ";
330 
331  } else {
332  $sql[] = "`$fieldname` = ".$this->prepareValue($fieldname, $sval).", ";
333  }
334  }
335  if ( !$changed ) return '';
336  // If no fields have changed, then we will just return an empty string for the query.
337  if ( $tableObj->isVersioned() ){
338  $versionField = $tableObj->getVersionField();
339  $sql[] = "`$versionField` = ifnull(`$versionField`,0)+1, ";
340  }
341 
342  $sql[count($sql)-1] = substr($sql[count($sql)-1], 0, strlen($sql[count($sql)-1])-2);
343  // chop off the trailing comma from the update clause
344  $vals = $record->snapshotExists() ? $record->snapshotKeys() : $record->getValues( array_keys($this->_table->keys()));
345  // If a snapshot has been set we will use its key values in the where clause
346 
347  if ( $key_vals === null ){
348  $query = unserialize(serialize($vals));
349  foreach ( array_keys($query) as $qkey){
350  $query[$qkey] = "=".$this->_serializer->serialize($qkey, $query[$qkey]);
351  }
352  } else {
353  $query = $key_vals;
354  foreach (array_keys($query) as $qkey){
355  $query[$qkey] = "=".$query[$qkey];
356  }
357  }
358 
359  $sql[] = " ".$this->_where($query);
360  $sql[] = " LIMIT 1";
361 
362 
363 
364  $sql = implode($sql);
365  //echo $sql;
366  $this->action = null;
367  return $sql;
368 
369  }
370 
371 
372 
373  function insert(&$record, $tablename=null){
374  $app =& Dataface_Application::getInstance();
375  $this->action = 'insert';
376  if ( !is_a($record, "Dataface_Record") ){
377  throw new Exception("First argument to QueryBuilder::insert() must be of type Dataface_Record, but received ".get_class($record), E_USER_ERROR);
378  }
379  // the keys are not complete... so this item does not exist.. create new record.
380  $tableObj =& Dataface_Table::loadTable($this->tablename($tablename));
381  $dbObj =& Dataface_DB::getInstance();
382  $fields = array_keys($this->_mutableFields);
383  $keys =& $this->_table->keys();
384  $insertedKeys = array();
385  $insertedValues = array();
386 
387  foreach ($this->_mutableFields as $key=>$field){
388  if ( @$field['ignore'] ) continue;
389  if ( $tableObj->isDate($key) ){
390  // We must take special care for dates.
391  if (isset($fieldArr)) unset($fieldArr);
392  $fieldArr =& $tableObj->getField($key);
393  if ( isset($fieldArr['timestamp']) and in_array(strtolower($fieldArr['timestamp']), array('insert','update')) ){
394  $insertedKeys[] = '`'.$key.'`';
395  $insertedValues[] = 'NOW()';
396 
397  continue;
398  }
399  }
400  if ( !$record->hasValue($key) ) continue;
401  $val = $record->getValue($key);
402  if ( strtolower($this->_mutableFields[$key]['Extra']) == 'auto_increment' && !$val ){
403  // This is a MySQL 5 fix. In MySQL 5 it doesn't like it when you put blank values into
404  // auto increment fields.
405  continue;
406  }
407  if ( !isset($val)) continue;
408  $sval = $this->_serializer->serialize($key, $record->getValue($key) );
409  //if ( !$field['value'] && in_array($key, array_keys($keys)) ) continue;
410  if ( $tableObj->isBlob($key) and @$app->_conf['multilingual_content'] ){
411  $blobID = $dbObj->registerBlob($sval);
412  $sval2 = "-=-=B".$blobID."=-=-";
413  } else {
414  $sval2 = $sval;
415  }
416 
417  if ( strlen(strval($sval2)) == 0 and strtolower($this->_mutableFields[$key]['Null']) == 'yes' ){
418  $insertedKeys[] = '`'.$key.'`';
419  $insertedValues[] = 'NULL';
420  //$sql .= 'NULL,';
421  } else {
422  $insertedKeys[] = '`'.$key.'`';
423 
424  $insertedValues[] = $this->prepareValue($key,$sval2);
425  //$sql .= "'".addslashes($sval2)."',";
426  }
427  }
428  $sql = "INSERT INTO `".$this->tablename($tablename)."` (".
429  implode(',', $insertedKeys).') VALUES ('.
430  implode(',', $insertedValues).')';
431  $this->action = null;
432  return $sql;
433  }
434 
435 
436  function prepareValue($fieldname, $value,$serialize=false){
437  $quotes = true;
438  if ( $serialize ) $value = $this->_serializer->serialize($fieldname, $value);
439  if ( in_array( strtolower($this->_table->getType($fieldname)), array('timestamp','datetime')) ){
440  $value = "ifnull(convert_tz('".addslashes($value)."','".addslashes(df_tz_or_offset())."','SYSTEM'),'".addslashes($value)."')";
441  $quotes = false;
442  }
443  if ( $quotes ) $value = "'".addslashes($value)."'";
444  $value = $this->_serializer->encrypt($fieldname,$value);
445  return $value;
446 
447  }
448 
449 
450 
451  function delete($query=array(), $nolimit=false, $tablename=null){
452  $this->action = 'delete';
453  if ( !isset($tablename) ) $tablename = $this->_table->tablename;
454  $table =& Dataface_Table::loadTable($tablename);
455 
456  $query = array_merge($this->_query, $query);
457  $tsql=$table->sql();
458  $parent =& $table->getParent();
459  if ( isset($tsql) or isset($parent)){
460  $talias = $tablename.'__dforiginal__';
461  $joinclause = array();
462  foreach ( array_keys($table->keys() ) as $tkey){
463  $joinclause[] = "`$talias`.`$tkey`=`$tablename`.`$tkey`";
464  }
465  $joinclause = implode(' AND ', $joinclause);
466  $from = "FROM `{$talias}` USING `{$tablename}` as `{$talias}` left join ".substr($this->_from($tablename), 5)." on ($joinclause)";
467 
468  } else {
469  $from = $this->_from($tablename);
470 
471  }
472  $where = $this->_where($query);
473  $limit = $this->_limit($query, $nolimit);
474  $ret = "DELETE ".$from;
475 
476  if ( strlen($where)>0 ) $ret .= ' '.$where;
477  if ( strlen($limit)>0 ) $ret .= ' '.$limit;
478  $this->action = null;
479  return trim($ret);
480 
481 
482  }
483 
484  function wc($tablename, $colname){
485  if ( in_array($this->action, array('select','delete', 'select_num_rows')) ){
486  return "`{$tablename}`.`{$colname}`";
487  } else {
488  return "`{$colname}`";
489  }
490  }
491 
492  function _fieldWhereClause(&$field, $value, &$use_where, $tableAlias=null){
493  $key = $field['Field'];
494  $where = '';
495  $table =& Dataface_Table::loadTable($field['tablename']);
496  $changeTable = false;
497  if ( $this->_table->tablename != $table->tablename ){
498  $changeTable = true;
499  $oldTable =& $this->_table;
500  unset($this->_table);
501  $this->_table =& $table;
502  $oldSerializer =& $this->_serializer;
503  unset($this->_serializer);
504  $this->_serializer = new Dataface_Serializer($table->tablename);
505  }
506  if ( !isset($tableAlias) ) $tableAlias = $table->tablename;
507  if ( is_array($value) ){
508  throw new Exception("Attempt to use array in query clause");
509  }
510  $words = explode(' OR ', $value);
511  if ( count($words) > 1){
512  $where .= '(';
513  $conj = 'OR';
514  } else {
515  $conj = 'AND';
516  }
517 
518  // A value with a prefix of '<>' indicates we are searching for values NOT equal to...
519  if ( isset($field['repeat']) and $field['repeat']){
520  $repeat = true;
521 
522  } else {
523  $repeat = false;
524  }
525  foreach ($words as $value){
526  if ( $value === '' ) continue;
527  // A value with a prefix of '=' indicates that this is an exact match
528  if ( $value{0}=='=' ){
529  $exact = true;
530  $value = substr($value,1);
531  } else {
532  $exact = false;
533  }
534  $factors = explode(' AND ', $value);
535  if ( count($factors) > 1 ){
536  $where .= '(';
537  }
538  foreach ($factors as $value){
539  if ( !$exact and (strpos($value, '!=')===0 or strpos($value, '<>') === 0)){
540  $value = substr($value, 2);
541  $value = $this->prepareValue( $key, $table->parse($key, $value), true );
542  if ( $repeat ){
543  $where .= $this->wc($tableAlias, $key)." NOT RLIKE CONCAT('[[:<:]]',$value,'[[:>:]]') AND ";
544  } else {
545  $where .= $this->wc($tableAlias, $key)." <> $value AND ";
546  }
547 
548  // A value with a prefix of '<' indicates that we are searching for values less than
549  // a field.
550  } else if ( !$exact and strpos($value,'<')===0){
551  if ( strpos($value,'=') === 1 ){
552  $value = substr($value,2);
553  $op = '<=';
554  } else {
555  $value = substr($value, 1);
556  $op = '<';
557  }
558  $value = $this->prepareValue( $key, $table->parse($key, $value), true );
559  $where .= $this->wc($tableAlias, $key)." $op $value AND ";
560 
561  // A value with a prefix of '>' indicates a greater than search
562  } else if ( !$exact and strpos($value, '>')===0 ) {
563  if ( strpos($value,'=') === 1 ){
564  $value = substr($value,2);
565  $op = '>=';
566  } else {
567  $value = substr($value, 1);
568  $op = '>';
569  }
570  $value = $this->prepareValue( $key, $table->parse($key, $value), true );
571  $where .= $this->wc($tableAlias, $key)." $op $value AND ";
572 
573 
574  // If the query term has '..' any where it is interpreted as a range search
575  } else if ( !$exact and strpos($value, '..')> 0 ){
576  list($low,$high) = explode('..',$value);
577  $low = trim($low); $high = trim($high);
578  $low = $this->prepareValue( $key, $table->parse($key, $low), true);
579  $high = $this->prepareValue( $key, $table->parse($key, $high), true);
580  $where .= $this->wc($tableAlias, $key)." >= $low AND ".$this->wc($tableAlias, $key)." <= $high AND ";
581  } else if ( !$exact and strpos($value, '~') === 0 ){
582  $value = substr($value,1);
583  $oldval = $value;
584  $oper = 'LIKE';
585  $value = $this->prepareValue( $key, $table->parse($key, $value), true);
586  if ( strlen($oldval) > 0 ){
587  $where .= $this->wc($tableAlias,$key)." $oper $value AND ";
588  } else {
589  $where .= '('.$this->wc($tableAlias,$key)." $oper '' OR ".$this->wc($tableAlias,$key)." IS NULL) AND ";
590  }
591 
592 
593  } else if ( $repeat ){
594  $value = $this->prepareValue( $key, $table->parse($key, $value), true);
595  $where .= $this->wc($tableAlias, $key)." RLIKE CONCAT('[[:<:]]',$value,'[[:>:]]') AND ";
596  }
597 
598  else if ( $this->_exactMatches || preg_match( '/int/i', $field['Type']) || $exact ){
599  $oldval = $value;
600  $oper = '=';
601  $value = $this->prepareValue( $key, $table->parse($key, $value), true);
602  if ( strlen($oldval) > 0 ){
603  $where .= $this->wc($tableAlias,$key)." $oper $value AND ";
604  } else {
605  $where .= '('.$this->wc($tableAlias,$key)." $oper '' OR ".$this->wc($tableAlias,$key)." IS NULL) AND ";
606  }
607  } else {
608  $value = $this->prepareValue( $key, $table->parse($key, $value), true);
609  $where .= $this->wc($tableAlias, $key)." LIKE CONCAT('%',$value,'%') AND ";
610  }
611  $use_where = true;
612  }
613  $where = substr($where, 0, strlen($where)-5);
614  if (count($factors) > 1){
615 
616  $where .= ')';
617  }
618  $where .= ' OR ';
619 
620 
621  }
622  $where = substr($where, 0, strlen($where)-4);
623  if ( count($words) > 1){
624 
625  $where .= ')';
626  }
627 
628  if ($changeTable){
629  unset($this->_table);
630  $this->_table =& $oldTable;
631  unset($this->_serializer);
632  $this->_serializer =& $oldSerializer;
633  }
634  return $where;
635 
636  }
637 
638 
644  function _where($query=array(), $merge=true){
645  if ( $merge ){
646  $query = array_merge( $this->_query, $query);
647 
648  }
649  foreach ($query as $key=>$value) {
650  if ( $value === null or $value === '' ){
651  unset($query[$key]);
652  }
653  }
654 
655  if ( isset($query['__id__']) ){
656  $keys = array_keys($this->_table->keys());
657  if ( $keys ){
658  $query[$keys[0]] = $query['__id__'];
659  unset($query['__id__']);
660  }
661  }
662 
663 
664 
665  $where = "WHERE ";
666  $missing_key = false;
667  $limit = '';
668  $use_where = false;
669 
670  $fields = array();
671  //print_r($query);
672  foreach ($query as $key=>$value){
673  if ( strpos($key,'-') !== 0 ) $fields[$key] = $value;
674  }
675  foreach ($fields as $key=>$value){
676  if ( isset($this->_fields[$key]) ){
677  $field =& $this->_fields[$key];
678  if ( !@$field['not_findable'] ){
679  $where .= $this->_fieldWhereClause($field, $value, $use_where, $this->_tablename).' AND ';
680  }
681  unset($field);
682  }
683 
684  }
685  $charFields = $this->_table->getCharFields(true, true);
686  if ( isset( $query['-search'] ) and strlen($query['-search']) and count($charFields)>0 ){
687  $status = $this->_table->getStatus();
688  //if ( $status['Engine'] == 'MyISAM' ){
689  // // MyISAM has a match clause. that works quite well.
690  // $where .= $this->_match($query['-search'])." AND ";
691  //} else {
692  // // If the table type is not MyISAM, then we need to manually do the multi-field search.
693  $words = explode(' ', $query['-search']);
694  foreach ( $words as $word ){
695  $where .= '(`'.implode('` LIKE \'%'.addslashes($word).'%\' OR `', $charFields).'` LIKE \'%'.addslashes($word).'%\') AND ';
696  // }
697  }
698 
699  $use_where = true;
700  }
701 
702  if ( $this->metadata ){
703  $wfkeys = preg_grep('/^_metadata::/', array_keys($query));
704  $clause = array();
705  foreach ($wfkeys as $wfkey){
706  $wf_col = substr($wfkey,11);
707  if ( !$this->_table->fieldExists($wf_col) ) continue;
708  $wf_col = $this->_tablename."__metadata.__{$wf_col}";
709  $clause[] = "`{$wf_col}`='".addslashes($query[$wfkey])."'";
710  }
711  if ( count($clause)>0 ){
712  $use_where = true;
713  $where .= implode(' AND ', $clause).' AND ';
714  }
715  }
716 
717  // Now we will search related fields
718  $rkeys = preg_grep('/^[^\-].*\/.*$/', array_keys($query));
719 
720  $rquery = array();
721  foreach ($rkeys as $rkey ){
722  list($relationship, $rfield) = explode('/', $rkey);
723  $rquery[$relationship][] = $rfield;
724 
725  }
726 
727  foreach ( $rquery as $rname=>$rfields){
728  $r =& $this->_table->getRelationship($rname);
729  if ( PEAR::isError($r) ){
730  unset($r);
731  continue;
732  }
733 
734 
735  $pairs=array();
736  foreach ( $rfields as $rfield ){
737  $rfieldDef =& $r->getField($rfield);
738  $q = $query[$rname.'/'.$rfield];
739  $ralias = $r->getTableAlias($rfieldDef['tablename']);
740  if ( !$ralias ) $ralias = null;
741  $pairs[] = $this->_fieldWhereClause($rfieldDef, $q, $use_where, $ralias );
742  unset($rfieldDef);
743 
744  //$pairs[] = '`'.str_replace('`','',$rfield).'` LIKE \'%'.addslashes($query[$rname.'/'.$rfield]).'%\'';
745  }
746  if ( $pairs ){
747  $subwhere = ' AND '.implode(' AND ',$pairs);
748  }
749 
750  $sql = $r->getSQL();
751 
752  $fkeys = $r->getForeignKeyValues();
753  foreach ( $fkeys as $tname=>$tfields ){
754  foreach ( $tfields as $tval ){
755  if ( !is_scalar($tval) ) continue;
756  if ( strlen($tval) > 0 ) $tval = substr($tval,1);
757  $sql = preg_replace('/[\'"]?\$('.preg_quote($tval).')[\'"]?/', '`'.str_replace('`','',$this->_table->tablename).'`.`\1`', $sql);
758  }
759  }
760  $where .= 'EXISTS ('.$sql.$subwhere.') AND ';
761  $use_where = true;
762  unset($r);
763  unset($fkeys);
764  }
765 
766 
767 
768  if ( $use_where ){
769 
770  $where = substr($where,0, strlen($where)-5);
771  } else {
772  $where = '';
773  }
774 
775  return $where;
776  }
777 
778 
779 
784  function _from($tablename=null){
785  $app =& Dataface_Application::getInstance();
786  if ( !isset($tablename) ) $tablename = $this->_table->tablename;
787 
788  $table =& Dataface_Table::loadTable($tablename);
789  $proxyView = $table->getProxyView();
790  $tsql = $table->sql();
791  $fromq = '';
792  if ( $proxyView ){
793  $fromq = "`".$proxyView."`";
794  } else if ( isset($tsql) ){
795  $fromq = "(".$tsql.")";
796  } else {
797  $fromq = "`".$this->tablename($tablename)."`";
798  }
799 
800  $parent =& $table->getParent();
801  if ( isset($parent) ){
802  $qb2 = new Dataface_QueryBuilder($parent->tablename);
803  $pfrom = $qb2->_from();
804  $as_pos = ( ( strpos(strtolower($pfrom), ' as ') !== false ) ? (strlen($pfrom) - strpos(strrev(strtolower($pfrom)), ' sa ' )-3) : false);
805  if ( $as_pos !== false ){
806  $pfrom = substr($pfrom, strlen('FROM '), $as_pos-strlen('FROM '));
807  } else {
808  $pfrom = substr($pfrom, strlen('FROM '));
809  }
810  $pkeys = array_keys($parent->keys());
811  $ckeys = array_keys($table->keys());
812  $joinq = array();
813  for ($i=0; $i<count($pkeys); $i++){
814  $joinq[] = '`t___child`.`'.$ckeys[$i].'`=`t___parent`.`'.$pkeys[$i].'`';
815  }
816  $joinq = implode(' and ', $joinq);
817 
818 
819  $out = "FROM (select * from ".$fromq." as `t___child` left join ".$pfrom." as `t___parent` on ($joinq)) as `".$this->tablename($tablename)."`" ;
820 
821 
822  } else if ( isset($tsql) or isset($proxyView) ){
823  $out = "FROM ".$fromq." as `".$this->tablename($tablename)."`";
824  } else {
825  $out = "FROM ".$fromq;
826  }
827 
828 
829 
830 
831  if ( $this->metadata and $this->action == 'select') {
832  $out .= " LEFT JOIN `{$tablename}__metadata` ON ";
833  $keys = array_keys($table->keys());
834  if ( count($keys) == 0 ) throw new Exception("The table '".$tablename."' has no primary key.", E_USER_ERROR);
835 
836  $clauses = array();
837  foreach ( $keys as $key ){
838  $clauses[] = "`{$tablename}`.`{$key}`=`{$tablename}__metadata`.`{$key}`";
839  }
840  $out .= "(".implode(' and ', $clauses).")";
841  }
842  return $out;
843  }
844 
845 
851  function _select($columns='', $query=array(), $preview=false, $previewLen=null){
852  if ( !isset($previewLen) and defined('XATAFACE_DEFAULT_PREVIEW_LENGTH') and is_int(XATAFACE_DEFAULT_PREVIEW_LENGTH) ){
853  $previewLen = XATAFACE_DEFAULT_PREVIEW_LENGTH;
854  }
855  if ( !is_int($previewLen) ) $previewLen = 255;
856  $app =& Dataface_Application::getInstance();
857  $query = array_merge( $this->_query, $query);
858  foreach ($query as $key=>$value) {
859  if ( $value === null ){
860  unset($query[$key]);
861  }
862  }
863  $select = "SELECT ";
864  $colcount = 0;
865  foreach ($this->_fields as $key=>$field){
866  if ( $this->selectMetaData ){
867  $select .= "length(`{$this->_tablename}`.`".$key."`) as `__".$key."_length`,";
868 
869  }
870  if ( is_array($columns) and !in_array($key, $columns) ) continue;
871  // if the columns array is set then we only return the columns listed in that array.
872 
873 
874 
875  if ( $this->_omitBlobs and $this->_table->isBlob($field['name']) ) continue;
876  // if the omitBlobs flag is set then we don't select blob columns
877  if ( $this->_omitPasswords and $this->_table->isPassword($field['name']) ) continue;
878  // if the omitPasswords flag is set then we don't select password columns
879  if ( $preview and $this->_table->isText($field['name']) and !@$field['struct'] and !$this->_table->isXML($field['name']))
880  $select .= "SUBSTRING(`{$this->_tablename}`.`$key`, 1, ".$previewLen.") as `$key`,";
881  else if ( in_array(strtolower($this->_table->getType($key)),array('datetime','timestamp')) )
882  $select .= "ifnull(convert_tz(`".$this->_tablename."`.`".$key."`, 'SYSTEM', '".df_tz_or_offset()."'), `".$this->_tablename."`.`".$key."`) as `$key`,";
883  else
884  $select .= "`{$this->_tablename}`.`$key`,";
885  $colcount++;
886 
887  }
888  if ( $this->metadata) {
889  $clauses = array();
890  foreach ( $this->_table->getMetadataColumns() as $mdc ){
891  $clauses[] = "`{$this->_tablename}__metadata`.`{$mdc}`";
892  }
893  $select .= implode(',',$clauses).',';
894 
895  }
896 
897  if ( $colcount == 0 ){
898  return PEAR::raiseError(QUERYBUILDER_ERROR_EMPTY_SELECT, null,null,null, "No columns were selected in select statement. Make sure that _omitBlobs property is disabled in QueryBuilder object if you are only wanting to return Blob columns.");
899  }
900  $select = substr($select, 0, strlen($select) -1);
901  return $select;
902  }
903 
908  function _limit($query=array(), $nolimit=false){
909  if ( $nolimit ) return '';
910  $query = array_merge( $this->_query, $query);
911  foreach ($query as $key=>$value) {
912  if ( $value === null ){
913  unset($query[$key]);
914  }
915  }
916 
917  $limit = '';
918  if ( isset( $query['-limit']) && isset($query['-skip'] ) ){
919  if ( preg_match('/^[0-9]+$/',$query['-limit']) &&
920  preg_match('/^[0-9]+$/',$query['-skip']) ){
921  $limit = "LIMIT ".$query['-skip'].",".$query['-limit'];
922  }
923  } else if ( isset( $query['-limit'] ) ){
924  if ( preg_match('/^[0-9]+$/', $query['-limit']) ){
925  $limit = "LIMIT ".$query['-limit'];
926  }
927  } else if ( isset( $query['-skip']) ){
928  if ( preg_match('/^[0-9]+$/', $query['-skip']) ){
929  $limit = "LIMIT ".$query['-skip'].", 100";
930  }
931  }
932  return $limit;
933  }
934 
935 
939  function _orderby($query = array()){
940  $query = array_merge( $this->_query, $query);
941  foreach ($query as $key=>$value) {
942  if ( $value === null ){
943  unset($query[$key]);
944  }
945  }
946 
947  if ( isset($query['-sort']) ){
948 
949  return 'ORDER BY '.preg_replace_callback('/\b(\w+?)\b/',array(&$this, '_mysql_quote_idents'), $query['-sort']);
950  }
951  return '';
952 
953  }
954 
955  function _mysql_quote_idents($matches){
956  if (!in_array(strtolower($matches[1]), array('asc','desc') ) ){
957  return '`'.((strpos($matches[1],'.') === false) ?"{$this->_tablename}`.`":'').$matches[1].'`';
958  } else {
959  return $matches[1];
960  }
961  }
962 
980  function _match($queryStr){
981  $version = mysql_get_server_info();
982  $matches = array();
983  preg_match('/(\d+)\.(\d)+\.(\d)+/', $version, $matches);
984  $majorVersion = intval($matches[1]);
985 
986  // We want to escape illegal characters, but in a boolean search
987  // double quotes are allowed so we much unescape them.
988  $queryStr = addslashes($queryStr);
989  $queryStr = str_replace('\"', '"', $queryStr);
990 
991  $out = 'MATCH (';
992 
993  // We have at least version 4 so we can do boolean searches
994  $indexedFields =& $this->_table->getFullTextIndexedFields();
995  if ( count($indexedFields)>0){
996  $fields =& $indexedFields;
997  } else {
998  // There are no indexed fields so we will just do a search on all character fields.
999  $fields =& $this->_table->getCharFields();
1000  }
1001 
1002 
1003  $empty = true;
1004  // flag to indicate if the query will be empty
1005  foreach ($fields as $field){
1006  $out .= "`{$this->_tablename}`.`$field`,";
1007  $empty = false;
1008  // the query is NOT empty
1009  }
1010 
1011  if ( $empty ){
1012  throw new Exception("Query attempted when no queryable columns are available in table '".$this->_table->tablename."'. Only tables with a full-text search defined on at least one column are eligiblle to be searched in this way.", E_USER_ERROR);
1013  }
1014 
1015  $out = substr($out, 0, strlen($out)-1);
1016 
1017  $out .= ") AGAINST ('$queryStr'";
1018  if ( $majorVersion >= 4 ) {
1019  $out .= " IN BOOLEAN MODE";
1020  }
1021  $out .= ")";
1022 
1023  return $out;
1024 
1025  }
1026 
1027 
1028  function omitBlobs(){
1029 
1030  $this->_omitBlobs = true;
1031  }
1032 
1033  function includeBlobs(){
1034  $this->_omitBlobs = false;
1035  }
1036 
1037 
1038  function addSecurityConstraint($key, $value){
1039 
1040  $this->_security[$key] = $value;
1041 
1042  }
1043 
1044  function addSecurityConstraints($constraints){
1045 
1046  $this->_security = array_merge($this->_security, $constraints);
1047  }
1048 
1049 
1050  function removeSecurityConstraint($key){
1051  unset( $this->_security[$key] );
1052  }
1053 
1054  function setSecurityConstraints( $constraints ){
1055  $this->_security = $constraints;
1056  }
1057 
1058  function _secure($where){
1059 
1060  $swhere = $this->_where($this->_table->getSecurityFilter($this->_security), false);
1061  // get rid of the leading "where"
1062  $swhere = trim(substr($swhere, 5, strlen($swhere)-5));
1063 
1064  $where = trim($where);
1065  if ( strlen($where)>0 ){
1066  if (strlen($swhere)>0) {
1067  $where .= " AND ".$swhere;
1068  }
1069  } else if ( strlen($swhere)>0){
1070  $where = "WHERE $swhere";
1071  }
1072  return $where;
1073 
1074 
1075 
1076  }
1077 
1078 
1082  function addRelatedRecord(&$relatedRecord, $sql=null){
1083  if ( !is_a($relatedRecord, 'Dataface_RelatedRecord') ){
1084  throw new Exception("In QueryBuilder::addRelatedRecord() expecting first argument to be type 'Dataface_RelatedRecord' but received '".get_class($relatedRecord)."'\n<br>", E_USER_ERROR);
1085  }
1086  $relationship =& $relatedRecord->_relationship;
1087  $table_cols = $relatedRecord->getForeignKeyValues( $sql);
1088  if ( count($this->errors) > 0 ){
1089  $error = array_pop($this->errors);
1090  $error->addUserInfo("Error getting foreign key values for relationship '$relationship_name'");
1091  throw new Exception($error->toString());
1092  }
1093 
1094 
1095  $sql = array();
1096 
1097  // now generate the sql
1098  // We will go through each table and insert the record for that
1099  // table separately.
1100  foreach ( $table_cols as $table=>$cols ){
1101  if ( isset($recordObj) ) unset($recordObj);
1102  $recordObj = new Dataface_Record($table, $cols);
1103  $recordVals =& $recordObj->vals();
1104  if ( isset( $recordVals[ $recordObj->_table->getAutoIncrementField() ] ) ){
1105  // We don't want the auto-increment field to be inserted - though it may
1106  // have a placeholder value.
1107  $recordObj->setValue($recordObj->_table->getAutoIncrementField(), null);
1108  }
1109  $qb = new Dataface_QueryBuilder($table);
1110  $sql[$table] = $qb->insert($recordObj);
1111 
1112  }
1113 
1114  return $sql;
1115 
1116  }
1117 
1118 
1119  function addExistingRelatedRecord(&$relatedRecord){
1120  $record =& $relatedRecord->_record;
1121  $relationshipName =& $relatedRecord->_relationshipName;
1122  $values = $relatedRecord->getAbsoluteValues(true);
1123  if ( !is_a($record, 'Dataface_Record') ){
1124  throw new Exception("In Dataface_QueryBuilder::addExistingRelatedRecord() expected first argument to be of type 'Dataface_Record' but received '".get_class($record)."'.\n<br>", E_USER_ERROR);
1125  }
1126  if ( !is_array($values) ){
1127  throw new Exception("In Dataface_QueryBuilder::addExistingRelatedRecord() expected third argument to be an array but received a scalar.", E_USER_ERROR);
1128  }
1129  $relationship =& $record->_table->getRelationship($relationshipName);
1130  $foreignKeys = $relationship->getForeignKeyValues();
1131  $foreignKeys_withValues = $relatedRecord->getForeignKeyValues();
1132 
1133  if ( count($this->errors) > 0 ){
1134  $error = array_pop($this->errors);
1135  $error->addUserInfo("Error getting foreign key values for relationship '$relationship_name'");
1136  throw new Exception($error->toString());
1137  }
1138 
1139  $sql = array();
1140  foreach ($foreignKeys as $table=>$cols){
1141  $skip = true;
1142  foreach ($cols as $field_name=>$field_value){
1143  if ( $field_value != "__".$table."__auto_increment__" ) {
1144  $skip = false;
1145  break;
1146  }
1147  }
1148  if ( $skip ) continue;
1149  $cols = $foreignKeys_withValues[$table];
1150  if ( isset($recordObj) ) unset($recordObj);
1151  $recordObj = new Dataface_Record($table, $cols);
1152  $recordVals =& $recordObj->vals();
1153  if ( isset( $recordVals[ $recordObj->_table->getAutoIncrementField() ] ) ){
1154  // We don't want the auto-increment field to be inserted - though it may
1155  // have a placeholder value.
1156  $recordObj->setValue($recordObj->_table->getAutoIncrementField(), null);
1157  }
1158  $qb = new Dataface_QueryBuilder($table);
1159  $sql[$table] = $qb->insert($recordObj);
1160  /*
1161  $skip = true;
1162  // indicator to say whether or not to skip this table
1163  // we skip the table if it contains an unresolved autoincrement value
1164 
1165  foreach ($cols as $field_name=>$field_value){
1166  if ( $field_value != "__".$table."__auto_increment__" ) {
1167  $skip = false;
1168  break;
1169  }
1170  }
1171 
1172  if ( $skip == true ) continue;
1173 
1174 
1175  $cols = $foreignKeys_withValues[$table];
1176 
1177 
1178  $query = "INSERT INTO `$table`";
1179  $colnames = "";
1180  $colvals = "";
1181 
1182  foreach ( $cols as $colname=>$colval){
1183  $colnames .= $colname.',';
1184  $colvals .= "'".addslashes($colval)."',";
1185  }
1186 
1187  $colnames = substr($colnames, 0, strlen($colnames)-1);
1188  $colvals = substr($colvals, 0, strlen($colvals)-1);
1189 
1190  $query .= " ($colnames) VALUES ($colvals)";
1191 
1192  $sql[$table] = $query;
1193  */
1194 
1195  }
1196 
1197  return $sql;
1198 
1199 
1200 
1201  }
1202 
1203  function tablename($tablename=null){
1204  if ( $tablename === null ) return $this->_tablename;
1205  return $tablename;
1206 
1207  }
1208 
1209 
1210 
1211 
1212 
1213 }
Generated on Fri Mar 22 2013 09:40:34 for Xataface by   doxygen 1.8.1.2