| [ Index ] |
|
Code source de Horde 3.1.3 |
1 <?php 2 /** 3 * This is a utility class, every method is static. 4 * 5 * $Horde: framework/SQL/SQL.php,v 1.30.2.17 2006/04/19 03:49:49 ben Exp $ 6 * 7 * Copyright 1999-2006 Chuck Hagenbuch <chuck@horde.org> 8 * Copyright 2005-2006 Jan Schneider <jan@horde.org> 9 * 10 * See the enclosed file COPYING for license information (LGPL). If you did 11 * not receive this file, see http://www.fsf.org/copyleft/lgpl.html. 12 * 13 * @author Chuck Hagenbuch <chuck@horde.org> 14 * @author Jan Schneider <jan@horde.org> 15 * @since Horde 2.2 16 * @package Horde_SQL 17 */ 18 class Horde_SQL { 19 20 /** 21 * Returns a boolean expression using the specified operator. Uses 22 * database-specific casting, if necessary. 23 * 24 * @param DB $dbh The PEAR::DB database object. 25 * @param string $lhs The column or expression to test. 26 * @param string $op The operator. 27 * @param string $rhs The comparison value. 28 * @param boolean $bind If true, the method returns the query and a list 29 * of values suitable for binding as an array. 30 * 31 * @return string The SQL test fragment. 32 */ 33 function buildClause(&$dbh, $lhs, $op, $rhs, $bind = false) 34 { 35 switch ($op) { 36 case '|': 37 case '&': 38 switch ($dbh->phptype) { 39 case 'pgsql': 40 // Only PgSQL 7.3+ understands SQL99 'SIMILAR TO'; use 41 // ~ for greater backwards compatibility. 42 $query = 'CASE WHEN CAST(%s AS VARCHAR) ~ \'^-?[0-9]+$\' THEN (CAST(%s AS INTEGER) %s %s) <> 0 ELSE FALSE END'; 43 if ($bind) { 44 return array(sprintf(Horde_SQL::escapePrepare($query), 45 Horde_SQL::escapePrepare($lhs), 46 Horde_SQL::escapePrepare($lhs), 47 Horde_SQL::escapePrepare($op), 48 '?'), 49 array((int)$rhs)); 50 } else { 51 return sprintf($query, $lhs, $lhs, $op, (int)$rhs); 52 } 53 54 case 'oci8': 55 // Oracle uses & for variables. We need to use the bitand 56 // function that is available, but may be unsupported. 57 $query = 'bitand(%s, %s) = %s'; 58 if ($bind) { 59 return array(sprintf(Horde_SQL::escapePrepare($query), 60 Horde_SQL::escapePrepare($lhs), '?', '?'), 61 array((int)$rhs, (int)$rhs)); 62 } else { 63 return sprintf($query, $lhs, (int)$rhs, (int)$rhs); 64 } 65 66 case 'mssql': 67 // MSSQL must have a valid boolean expression 68 $query = '(CASE WHEN ISNUMERIC(%s) = 1 THEN (%s & %s) ELSE %s END) = %s'; 69 if ($bind) { 70 return array(sprintf(Horde_SQL::escapePrepare($query), 71 Horde_SQL::escapePrepare($lhs), 72 Horde_SQL::escapePrepare($lhs), '?', '?', '?'), 73 array((int)$rhs, (int)$rhs - 1, (int)$rhs)); 74 } else { 75 return sprintf($query, $lhs, $lhs, (int)$rhs, (int)$rhs - 1, (int)$rhs); 76 } 77 78 case 'odbc': 79 // ODBC must have a valid boolean expression 80 $query = '(%s & %s) = %s'; 81 if ($bind) { 82 return array(sprintf(Horde_SQL::escapePrepare($query), 83 Horde_SQL::escapePrepare($lhs), '?', '?'), 84 array((int)$rhs, (int)$rhs)); 85 } else { 86 return sprintf($query, $lhs, (int)$rhs, (int)$rhs); 87 } 88 89 default: 90 if ($bind) { 91 return array($lhs . ' ' . Horde_SQL::escapePrepare($op) . ' ?', 92 array((int)$rhs)); 93 } else { 94 return $lhs . ' ' . $op . ' ' . (int)$rhs; 95 } 96 } 97 98 case '~': 99 if ($dbh->phptype == 'mysql') { 100 $op = 'REGEXP'; 101 } 102 if ($bind) { 103 return array($lhs . ' ' . $op . ' ?', array($rhs)); 104 } else { 105 return $lhs . ' ' . $op . ' ' . $rhs; 106 } 107 108 case 'IN': 109 if ($bind) { 110 if (is_array($rhs)) { 111 return array($lhs . ' IN (?' . str_repeat(', ?', count($rhs) - 1) . ')', $rhs); 112 } else { 113 // We need to bind each member of the IN clause 114 // separately to ensure proper quoting. 115 if (substr($rhs, 0, 1) == '(') { 116 $rhs = substr($rhs, 1); 117 } 118 if (substr($rhs, -1) == ')') { 119 $rhs = substr($rhs, 0, -1); 120 } 121 122 $ids = preg_split('/\s*,\s*/', $rhs); 123 124 return array($lhs . ' IN (?' . str_repeat(', ?', count($ids) - 1) . ')', $ids); 125 } 126 } else { 127 if (is_array($rhs)) { 128 return $lhs . ' IN ' . implode(', ', $rhs); 129 } else { 130 return $lhs . ' IN ' . $rhs; 131 } 132 } 133 134 case 'LIKE': 135 if ($dbh->phptype == 'pgsql') { 136 $query = '%s ILIKE %s'; 137 } else { 138 $query = 'LOWER(%s) LIKE LOWER(%s)'; 139 } 140 if ($bind) { 141 return array(sprintf($query, Horde_SQL::escapePrepare($lhs), '?'), array('%' . $rhs . '%')); 142 } else { 143 return sprintf($query, $lhs, $dbh->quote('%' . $rhs . '%')); 144 } 145 146 default: 147 if ($bind) { 148 return array($lhs . ' ' . Horde_SQL::escapePrepare($op) . ' ?', array($rhs)); 149 } else { 150 return $lhs . ' ' . $op . ' ' . $dbh->quote($rhs); 151 } 152 } 153 } 154 155 /** 156 * Escapes all characters in a string that are placeholders for the 157 * prepare/execute methods of the DB package. 158 * 159 * @param string $query A string to escape. 160 * 161 * @return string The correctly escaped string. 162 */ 163 function escapePrepare($query) 164 { 165 return preg_replace('/[?!&]/', '\\\\$0', $query); 166 } 167 168 function readBlob(&$dbh, $table, $field, $criteria) 169 { 170 if (!count($criteria)) { 171 return PEAR::raiseError('You must specify the fetch criteria'); 172 } 173 174 $where = ''; 175 176 switch ($dbh->dbsyntax) { 177 case 'oci8': 178 foreach ($criteria as $key => $value) { 179 if (!empty($where)) { 180 $where .= ' AND '; 181 } 182 if (empty($value)) { 183 $where .= $key . ' IS NULL'; 184 } else { 185 $where .= $key . ' = ' . $dbh->quote($value); 186 } 187 } 188 189 $statement = OCIParse($dbh->connection, 190 sprintf('SELECT %s FROM %s WHERE %s', 191 $field, $table, $where)); 192 OCIExecute($statement); 193 if (OCIFetchInto($statement, $lob)) { 194 $result = $lob[0]->load(); 195 } else { 196 $result = PEAR::raiseError('Unable to load SQL Data'); 197 } 198 OCIFreeStatement($statement); 199 break; 200 201 default: 202 foreach ($criteria as $key => $value) { 203 if (!empty($where)) { 204 $where .= ' AND '; 205 } 206 $where .= $key . ' = ' . $dbh->quote($value); 207 } 208 $result = $dbh->getOne(sprintf('SELECT %s FROM %s WHERE %s', 209 $field, $table, $where)); 210 211 switch ($dbh->dbsyntax) { 212 case 'pgsql': 213 $result = pack('H' . strlen($result), $result); 214 break; 215 } 216 } 217 218 return $result; 219 } 220 221 function insertBlob(&$dbh, $table, $field, $data, $attributes) 222 { 223 $fields = array(); 224 $values = array(); 225 226 switch ($dbh->dbsyntax) { 227 case 'oci8': 228 foreach ($attributes as $key => $value) { 229 $fields[] = $key; 230 $values[] = $dbh->quote($value); 231 } 232 233 $statement = OCIParse($dbh->connection, 234 sprintf('INSERT INTO %s (%s, %s)' . 235 ' VALUES (%s, EMPTY_BLOB()) RETURNING %s INTO :blob', 236 $table, 237 implode(', ', $fields), 238 $field, 239 implode(', ', $values), 240 $field)); 241 242 $lob = OCINewDescriptor($dbh->connection); 243 OCIBindByName($statement, ':blob', $lob, -1, SQLT_BLOB); 244 OCIExecute($statement, OCI_DEFAULT); 245 $lob->save($data); 246 $result = OCICommit($dbh->connection); 247 $lob->free(); 248 OCIFreeStatement($statement); 249 return $result ? true : PEAR::raiseError('Unknown Error'); 250 251 default: 252 foreach ($attributes as $key => $value) { 253 $fields[] = $key; 254 $values[] = $value; 255 } 256 257 $query = sprintf('INSERT INTO %s (%s, %s) VALUES (%s)', 258 $table, 259 implode(', ', $fields), 260 $field, 261 '?' . str_repeat(', ?', count($values))); 262 break; 263 } 264 265 switch ($dbh->dbsyntax) { 266 case 'mssql': 267 case 'pgsql': 268 $values[] = bin2hex($data); 269 break; 270 271 default: 272 $values[] = $data; 273 } 274 275 /* Log the query at a DEBUG log level. */ 276 Horde::logMessage(sprintf('SQL Query by Horde_SQL::insertBlob(): query = "%s"', $query), 277 __FILE__, __LINE__, PEAR_LOG_DEBUG); 278 279 /* Execute the query. */ 280 return $this->_db->query($query, $values); 281 } 282 283 function updateBlob(&$dbh, $table, $field, $data, $where, $alsoupdate) 284 { 285 $fields = array(); 286 $values = array(); 287 288 switch ($dbh->dbsyntax) { 289 case 'oci8': 290 $wherestring = ''; 291 foreach ($where as $key => $value) { 292 if (!empty($wherestring)) { 293 $wherestring .= ' AND '; 294 } 295 $wherestring .= $key . ' = ' . $dbh->quote($value); 296 } 297 298 $statement = OCIParse($dbh->connection, 299 sprintf('SELECT %s FROM %s WHERE %s FOR UPDATE', 300 $field, 301 $table, 302 $wherestring)); 303 304 OCIExecute($statement, OCI_DEFAULT); 305 OCIFetchInto($statement, $lob); 306 $lob[0]->save($data); 307 $result = OCICommit($dbh->connection); 308 $lob[0]->free(); 309 OCIFreeStatement($statement); 310 return $result ? true : PEAR::raiseError('Unknown Error'); 311 312 default: 313 $updatestring = ''; 314 $values = array(); 315 foreach ($alsoupdate as $key => $value) { 316 $updatestring .= $key . ' = ?, '; 317 $values[] = $value; 318 } 319 $updatestring .= $field . ' = ?'; 320 switch ($dbh->dbsyntax) { 321 case 'mssql': 322 case 'pgsql': 323 $values[] = bin2hex($data); 324 break; 325 326 default: 327 $values[] = $data; 328 } 329 330 $wherestring = ''; 331 foreach ($where as $key => $value) { 332 if (!empty($wherestring)) { 333 $wherestring .= ' AND '; 334 } 335 $wherestring .= $key . ' = ?'; 336 $values[] = $value; 337 } 338 339 $query = sprintf('UPDATE %s SET %s WHERE %s', 340 $table, 341 $updatestring, 342 $wherestring); 343 break; 344 } 345 346 /* Log the query at a DEBUG log level. */ 347 Horde::logMessage(sprintf('SQL Query by Horde_SQL::updateBlob(): query = "%s"', $query), 348 __FILE__, __LINE__, PEAR_LOG_DEBUG); 349 350 /* Execute the query. */ 351 return $dbh->query($query, $values); 352 } 353 354 /** 355 * Build an SQL SET clause. 356 * 357 * This function takes an array in the form column => value and returns 358 * an SQL SET clause (without the SET keyword) with the values properly 359 * quoted. For example, the following: 360 * 361 * array('foo' => 1, 362 * 'bar' => 'hello') 363 * 364 * would result in the fragment: 365 * 366 * foo = 1, bar = 'hello' 367 * 368 * @param DB $dbh The PEAR::DB database object. 369 * @param array $values The array of column => value pairs. 370 * 371 * @return string The SQL SET fragment. 372 */ 373 function updateValues(&$dbh, $values) 374 { 375 $ret = array(); 376 foreach ($values as $key => $value) { 377 $ret[] = $key . ' = ' . ($value === null ? 'NULL' : $dbh->quote($value)); 378 } 379 return implode(', ', $ret); 380 } 381 382 /** 383 * Build an SQL INSERT/VALUES clause. 384 * 385 * This function takes an array in the form column => value and returns 386 * an SQL fragment specifying the column names and insert values, with 387 * the values properly quoted. For example, the following: 388 * 389 * array('foo' => 1, 390 * 'bar' => 'hello') 391 * 392 * would result in the fragment: 393 * 394 * ( foo, bar ) VALUES ( 1, 'hello' ) 395 * 396 * @param DB $dbh The PEAR::DB database object. 397 * @param array $values The array of column => value pairs. 398 * 399 * @return string The SQL fragment. 400 */ 401 function insertValues(&$dbh, $values) 402 { 403 $columns = array(); 404 $vals = array(); 405 foreach ($values as $key => $value) { 406 $columns[] = $key; 407 $vals[] = $value === null ? 'NULL' : $dbh->quote($value); 408 } 409 return '( ' . implode(', ', $columns) . ' ) VALUES ( ' . implode(', ', $vals) . ' )'; 410 } 411 412 }
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Sun Feb 25 18:01:28 2007 | par Balluche grâce à PHPXref 0.7 |