1<?php 2 /** 3 * SQL Generator OPERANDS & TYPES - help to create criterias for common queries 4 * @author Edgar Antonio Luna Diaz <eald@co.com.mx> 5 * @author Alejadro Borges 6 * @author Jonathan Alberto Rivera Gomez 7 * @copyright Copyright (C) 2003,2004 Free Software Foundation, Inc. http://www.fsf.org/ 8 * @license http://www.fsf.org/licenses/gpl.html GNU General Public License 9 * @package phpgwapi 10 * @subpackage database 11 * @version $Id: class.sql.inc.php 21045 2010-03-25 22:41:38Z Caeies $ 12 * @internal Development of this application was funded by http://www.sogrp.com 13 * @link http://www.sogrp.com/ 14 */ 15 16 if (empty($GLOBALS['phpgw_info']['server']['db_type'])) 17 { 18 $GLOBALS['phpgw_info']['server']['db_type'] = 'mysql'; 19 } 20 21 /** 22 * Include concrete database class 23 */ 24 include(PHPGW_API_INC.'/class.sql_'.$GLOBALS['phpgw_info']['server']['db_type'].'.inc.php'); 25 26 27 /** 28 * SQL Generator OPERANDS & TYPES - help to create criterias for common queries 29 * 30 * This class provide common methods to set, mantain, an retrive the queries 31 * to use in a query (for the where clause). 32 * @package phpgwapi 33 * @subpackage database 34 * @abstract 35 */ 36 class sql_ 37 { 38 function sql_() 39 { 40 } 41 42 /*************************************************************\ 43 * Usefull low level functions to create queries logically * 44 \*************************************************************/ 45 46 /** 47 * Genarete equal criteria for sql 48 * 49 * @param string $left The left operand of the staement 50 * @param string $right The right operand of the statement 51 * @return string with an equal criteria formated. 52 */ 53 function equal($field, $value) 54 { 55 return $field.' = '.$value; 56 } 57 58 /** 59 * Generate a critieria for non equal comparission for sql. 60 * 61 * @param string $left Left operand. 62 * @param string $right Right operand. 63 * @return string with criteria. 64 */ 65 function not_equal($field, $value) 66 { 67 return $field.' <> '.$value; 68 } 69 70 /** 71 * Generate greater than criteria for sql 72 * 73 * @param string $left The left operand of the staement 74 * @param string $right The right operand of the statement 75 * @return string with an greater than criteria formated. 76 */ 77 function greater($field, $value) 78 { 79 return $field.' > '.$value; 80 } 81 82 /** 83 * Generate less than criteria for sql (in string) 84 * 85 * @param string $left The left operand of the staement 86 * @param string $right The right operand of the statement 87 * @return string with an less than criteria formated. 88 */ 89 function less($field, $value) 90 { 91 return $field.' < '.$value; 92 } 93 94 /** 95 * Generate greater-equal than criteria for sql 96 * 97 * @param string $left The left operand of the staement 98 * @param string $right The right operand of the statement 99 * @return string with an greater-equal than criteria formated. 100 */ 101 function greater_equal($field, $value) 102 { 103 return $field.' >= '.$value; 104 } 105 106 /** 107 * Generate less-equal than criteria for sql (in string) 108 * 109 * @param string $left The left operand of the staement 110 * @param string $right The right operand of the statement 111 * @return string with an less-equal than criteria formated. 112 */ 113 function less_equal($field, $value) 114 { 115 return $field.' <= '.$value; 116 } 117 118 /** 119 * Generate a criteria for search in the content of a field a value for sql. 120 * 121 * @param string $field For search in. 122 * @param string $value That will search. 123 * @return string that use LIKE to search in field. 124 */ 125 function has($field, $value) 126 { 127 return sql_criteria::upper($field).' LIKE '."'%$value%'"; 128 } 129 130 /** 131 * Generate a criteria to search in the beginning of a field for sql. 132 * 133 * @param string $field For search in. 134 * @param string $value That will search. 135 * @return string that use LIKE to search in field. 136 */ 137 function begin_with($field, $value) 138 { 139 return sql_criteria::upper($field).' LIKE '."'$value%'"; 140 } 141 142 /** 143 * Generate a critieria to search in the end of a field for sql. 144 * 145 * @param string $field For search in. 146 * @param string $value That will search. 147 * @return string that use LIKE to search in field. 148 */ 149 function end_with($field, $value) 150 { 151 return sql_criteria::upper($field).' LIKE '."'%$value'"; 152 } 153 154 /** 155 * Generate an AND conjuction for sql criterias. 156 * 157 * Always return with brackets. I have more confidence in DBMS speed than the code that I will need to analize it in php. 158 * @param string $left Left operand. 159 * @param string $right Right operand. 160 * @return string with (right) and (left) 161 */ 162 function and_($left, $right) 163 { 164 return '('.$left.' AND '.$right.')'; 165 } 166 167 /** 168 * Generate an OR conjuction for sql criterias. 169 * 170 * @param string $left Left operand. 171 * @param string $right Right operand. 172 * @return string with (right) or (left) 173 */ 174 function or_($left, $right) 175 { 176 return ' ('.$left.' OR '.$right.') '; 177 } 178 179 /** 180 * Generate a is null critieria for sql. 181 * 182 * @param string $data A field. 183 * @return string with criteria. 184 */ 185 function is_null($data) 186 { 187 return $data.' IS NULL'; 188 } 189 190 /** 191 * Generate a is not null critieria for sql. 192 * 193 * @param string $data A field. 194 * @return string with criteria. 195 */ 196 function not_null($data) 197 { 198 return $data.' IS NOT NULL'; 199 } 200 201 function upper($value) 202 { 203 return 'UPPER('.$value.')'; 204 } 205 206 function lower($value) 207 { 208 return 'LOWER('.$value.')'; 209 } 210 211 /** 212 * Generate a IN sql operator 213 * 214 * @param string $field String with the field which you can filter. 215 * @param string $values Array with posible values 216 * @return string with criteria. 217 */ 218 function in($field, $values, $type='integer') 219 { 220 // This must be changed by anything 221 if(count($values) > 1) 222 { 223 if($type != 'integer' && $type != '') 224 { 225 return str_replace(',\'', '\',\'', $field.' IN (\''.implode(",'",$values)."')"); 226 } 227 else 228 { 229 return $field.' IN ('.implode(",", $values) .')'; 230 } 231 } 232 else 233 { 234 $type = $type ? $type : 'integer'; 235 if (is_array($values)) 236 { 237 return sql::equal($field, sql::$type(current($values))); 238 } 239 else 240 { 241 return sql::equal($field, sql::$type($values)); 242 } 243 } 244 } 245 246 /** 247 * Act like a lisp and(one, two, three,...infinity) adding clause with and. 248 * 249 * All and's are in same level, (without parenethesis). 250 * @param string $and Array with the list of operators for and. 251 * @return string with many and conjuntions at same level. 252 */ 253 function append_and($clause) 254 { 255 if(is_array($clause)) 256 { 257 $value = array_shift($clause); 258 $return_value = $value; 259 foreach($clause as $element) 260 { 261 $return_value .= empty($element)?'':' AND '.$element; 262 } 263 return '('. $return_value .')'; 264 } 265 } 266 267 /** 268 * Append many criterias with `or' conjuntion 269 * 270 * @param string $and Array with the list of operators for or. 271 * @return string with many or conjuntions at same level. 272 * @see append_and 273 */ 274 function append_or($clause) 275 { 276 if(is_array($clause)) 277 { 278 $value = array_shift($clause); 279 $return_value = $value; 280 foreach($clause as $element) 281 { 282 $return_value .= empty($element)?'':' OR '.$element; 283 } 284 return '('. $return_value.')'; 285 } 286 } 287 /*************************************************************\ 288 * SQL Standard Types * 289 \*************************************************************/ 290 291 /** 292 * @param str string the value that will be casted for sql type 293 * @return string ready for using for a value with CHARACTER sql type 294 */ 295 function string($str) 296 { 297 $str = db::db_addslashes($str); 298 return "'$str'"; 299 } 300 301 function character($str) 302 { 303 return sql::string($str); 304 } 305 306 /** 307 * @param integer string the value that will be casted for sql type 308 * @return string ready for using for a value with INTEGER sql type 309 */ 310 function integer($integer) 311 { 312 return intval($integer); 313 } 314 315 /** 316 * Generate a string with date 317 */ 318 function date_($date, $format=False) 319 { 320 switch(gettype($date)) 321 { 322 case 'integer': 323 return sql::int_date2str($date, $format); 324 default: 325 return sql::str_date2int($date, $format); 326 } 327 } 328 329 /** 330 * return a string with time 331 */ 332 function time_($time, $format=False) 333 { 334 switch(gettype($time)) 335 { 336 case 'integer': 337 return sql::int_time2str($time, $format); 338 default: 339 return sql::str_time2int($time, $format); 340 } 341 } 342 343 /*************************************************************\ 344 * Data types conversion * 345 \*************************************************************/ 346 347 function int_date2str($int, $format=False) 348 { 349 $format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat']; 350 return date($format, intval($int)); 351 } 352 353 function int_time2str($int, $format=False) 354 { 355 $format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['timeformat']; 356 return date($format, intval($int)); 357 } 358 //note this is not 100% reliable, but close enough 359 function str_date2int($date, $format=False) 360 { 361 $format = $format ? $format : $GLOBALS['phpgw_info']['user']['preferences']['common']['dateformat']; 362 return date($format, intval(strtotime($date))); 363 } 364 365 function str_time2int($time) 366 { 367 return intval(sql::str_date2int($time)); 368 } 369 370 /*************************************************************\ 371 * Constants * 372 \*************************************************************/ 373 374 /** 375 * Return a NULL value 376 */ 377 function null() 378 { 379 return ' NULL '; 380 } 381 /*************************************************************\ 382 * Functions * 383 \*************************************************************/ 384 /** 385 * Return the function that concatenate fields on $elements 386 * 387 * @param array $elements array with the elemnts that want to concatenate 388 * @return string with $elements concatenated 389 */ 390 function concat($elements) 391 { 392 } 393 394 /** 395 * Return the function that concatenate fields, when any returned value<br /> 396 * is null, it changet it for empty string. 397 * 398 * @param array $elements array with the elemnts that want to concatenate 399 * @return string with $elements concatenated 400 */ 401 function concat_null($elements) 402 { 403 } 404 405 /** 406 * This function change to empty string, a NULL value for select. 407 * 408 * When data retrieved from database is NULL it allow change it to empty<br /> 409 * string. use it in SELECT development. 410 * @param string $value Field or expresion to make safe. 411 */ 412 function safe_null($value) 413 { 414 if(empty($value) || !is_array($value)) 415 { 416 return array(); 417 } 418 foreach($value as $data) 419 { 420 $return_value[] = '(CASE WHEN '.$data.' IS NULL THEN \'\' ELSE '.$data.' END)'; 421 } 422 return $return_value; 423 } 424 } 425?> 426