1<?php defined('SYSPATH') OR die('No direct access allowed.'); 2/** 3 * Database API driver 4 * 5 * $Id: Database.php 4343 2009-05-08 17:04:48Z jheathco $ 6 * 7 * @package Core 8 * @author Kohana Team 9 * @copyright (c) 2007-2008 Kohana Team 10 * @license http://kohanaphp.com/license.html 11 */ 12abstract class Database_Driver { 13 14 protected $query_cache; 15 16 /** 17 * Connect to our database. 18 * Returns FALSE on failure or a MySQL resource. 19 * 20 * @return mixed 21 */ 22 abstract public function connect(); 23 24 /** 25 * Perform a query based on a manually written query. 26 * 27 * @param string SQL query to execute 28 * @return Database_Result 29 */ 30 abstract public function query($sql); 31 32 /** 33 * Builds a DELETE query. 34 * 35 * @param string table name 36 * @param array where clause 37 * @return string 38 */ 39 public function delete($table, $where) 40 { 41 return 'DELETE FROM '.$this->escape_table($table).' WHERE '.implode(' ', $where); 42 } 43 44 /** 45 * Builds an UPDATE query. 46 * 47 * @param string table name 48 * @param array key => value pairs 49 * @param array where clause 50 * @return string 51 */ 52 public function update($table, $values, $where) 53 { 54 foreach ($values as $key => $val) 55 { 56 $valstr[] = $this->escape_column($key).' = '.$val; 57 } 58 return 'UPDATE '.$this->escape_table($table).' SET '.implode(', ', $valstr).' WHERE '.implode(' ',$where); 59 } 60 61 /** 62 * Set the charset using 'SET NAMES <charset>'. 63 * 64 * @param string character set to use 65 */ 66 public function set_charset($charset) 67 { 68 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__); 69 } 70 71 /** 72 * Wrap the tablename in backticks, has support for: table.field syntax. 73 * 74 * @param string table name 75 * @return string 76 */ 77 abstract public function escape_table($table); 78 79 /** 80 * Escape a column/field name, has support for special commands. 81 * 82 * @param string column name 83 * @return string 84 */ 85 abstract public function escape_column($column); 86 87 /** 88 * Builds a WHERE portion of a query. 89 * 90 * @param mixed key 91 * @param string value 92 * @param string type 93 * @param int number of where clauses 94 * @param boolean escape the value 95 * @return string 96 */ 97 public function where($key, $value, $type, $num_wheres, $quote) 98 { 99 $prefix = ($num_wheres == 0) ? '' : $type; 100 101 if ($quote === -1) 102 { 103 $value = ''; 104 } 105 else 106 { 107 if ($value === NULL) 108 { 109 if ( ! $this->has_operator($key)) 110 { 111 $key .= ' IS'; 112 } 113 114 $value = ' NULL'; 115 } 116 elseif (is_bool($value)) 117 { 118 if ( ! $this->has_operator($key)) 119 { 120 $key .= ' ='; 121 } 122 123 $value = ($value == TRUE) ? ' 1' : ' 0'; 124 } 125 else 126 { 127 if ( ! $this->has_operator($key) AND ! empty($key)) 128 { 129 $key = $this->escape_column($key).' ='; 130 } 131 else 132 { 133 preg_match('/^(.+?)([<>!=]+|\bIS(?:\s+NULL))\s*$/i', $key, $matches); 134 if (isset($matches[1]) AND isset($matches[2])) 135 { 136 $key = $this->escape_column(trim($matches[1])).' '.trim($matches[2]); 137 } 138 } 139 140 $value = ' '.(($quote == TRUE) ? $this->escape($value) : $value); 141 } 142 } 143 144 return $prefix.$key.$value; 145 } 146 147 /** 148 * Builds a LIKE portion of a query. 149 * 150 * @param mixed field name 151 * @param string value to match with field 152 * @param boolean add wildcards before and after the match 153 * @param string clause type (AND or OR) 154 * @param int number of likes 155 * @return string 156 */ 157 public function like($field, $match, $auto, $type, $num_likes) 158 { 159 $prefix = ($num_likes == 0) ? '' : $type; 160 161 $match = $this->escape_str($match); 162 163 if ($auto === TRUE) 164 { 165 // Add the start and end quotes 166 $match = '%'.str_replace('%', '\\%', $match).'%'; 167 } 168 169 return $prefix.' '.$this->escape_column($field).' LIKE \''.$match . '\''; 170 } 171 172 /** 173 * Builds a NOT LIKE portion of a query. 174 * 175 * @param mixed field name 176 * @param string value to match with field 177 * @param string clause type (AND or OR) 178 * @param int number of likes 179 * @return string 180 */ 181 public function notlike($field, $match, $auto, $type, $num_likes) 182 { 183 $prefix = ($num_likes == 0) ? '' : $type; 184 185 $match = $this->escape_str($match); 186 187 if ($auto === TRUE) 188 { 189 // Add the start and end quotes 190 $match = '%'.$match.'%'; 191 } 192 193 return $prefix.' '.$this->escape_column($field).' NOT LIKE \''.$match.'\''; 194 } 195 196 /** 197 * Builds a REGEX portion of a query. 198 * 199 * @param string field name 200 * @param string value to match with field 201 * @param string clause type (AND or OR) 202 * @param integer number of regexes 203 * @return string 204 */ 205 public function regex($field, $match, $type, $num_regexs) 206 { 207 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__); 208 } 209 210 /** 211 * Builds a NOT REGEX portion of a query. 212 * 213 * @param string field name 214 * @param string value to match with field 215 * @param string clause type (AND or OR) 216 * @param integer number of regexes 217 * @return string 218 */ 219 public function notregex($field, $match, $type, $num_regexs) 220 { 221 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__); 222 } 223 224 /** 225 * Builds an INSERT query. 226 * 227 * @param string table name 228 * @param array keys 229 * @param array values 230 * @return string 231 */ 232 public function insert($table, $keys, $values) 233 { 234 // Escape the column names 235 foreach ($keys as $key => $value) 236 { 237 $keys[$key] = $this->escape_column($value); 238 } 239 return 'INSERT INTO '.$this->escape_table($table).' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')'; 240 } 241 242 /** 243 * Builds a MERGE portion of a query. 244 * 245 * @param string table name 246 * @param array keys 247 * @param array values 248 * @return string 249 */ 250 public function merge($table, $keys, $values) 251 { 252 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__); 253 } 254 255 /** 256 * Builds a LIMIT portion of a query. 257 * 258 * @param integer limit 259 * @param integer offset 260 * @return string 261 */ 262 abstract public function limit($limit, $offset = 0); 263 264 /** 265 * Creates a prepared statement. 266 * 267 * @param string SQL query 268 * @return Database_Stmt 269 */ 270 public function stmt_prepare($sql = '') 271 { 272 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__); 273 } 274 275 /** 276 * Compiles the SELECT statement. 277 * Generates a query string based on which functions were used. 278 * Should not be called directly, the get() function calls it. 279 * 280 * @param array select query values 281 * @return string 282 */ 283 abstract public function compile_select($database); 284 285 /** 286 * Determines if the string has an arithmetic operator in it. 287 * 288 * @param string string to check 289 * @return boolean 290 */ 291 public function has_operator($str) 292 { 293 return (bool) preg_match('/[<>!=]|\sIS(?:\s+NOT\s+)?\b|BETWEEN/i', trim($str)); 294 } 295 296 /** 297 * Escapes any input value. 298 * 299 * @param mixed value to escape 300 * @return string 301 */ 302 public function escape($value) 303 { 304 if ( ! $this->db_config['escape']) 305 return $value; 306 307 switch (gettype($value)) 308 { 309 case 'string': 310 $value = '\''.$this->escape_str($value).'\''; 311 break; 312 case 'boolean': 313 $value = (int) $value; 314 break; 315 case 'double': 316 // Convert to non-locale aware float to prevent possible commas 317 $value = sprintf('%F', $value); 318 break; 319 default: 320 $value = ($value === NULL) ? 'NULL' : $value; 321 break; 322 } 323 324 return (string) $value; 325 } 326 327 /** 328 * Escapes a string for a query. 329 * 330 * @param mixed value to escape 331 * @return string 332 */ 333 abstract public function escape_str($str); 334 335 /** 336 * Lists all tables in the database. 337 * 338 * @return array 339 */ 340 abstract public function list_tables(); 341 342 /** 343 * Lists all fields in a table. 344 * 345 * @param string table name 346 * @return array 347 */ 348 abstract function list_fields($table); 349 350 /** 351 * Returns the last database error. 352 * 353 * @return string 354 */ 355 abstract public function show_error(); 356 357 /** 358 * Returns field data about a table. 359 * 360 * @param string table name 361 * @return array 362 */ 363 abstract public function field_data($table); 364 365 /** 366 * Fetches SQL type information about a field, in a generic format. 367 * 368 * @param string field datatype 369 * @return array 370 */ 371 protected function sql_type($str) 372 { 373 static $sql_types; 374 375 if ($sql_types === NULL) 376 { 377 // Load SQL data types 378 $sql_types = Kohana::config('sql_types'); 379 } 380 381 $str = strtolower(trim($str)); 382 383 if (($open = strpos($str, '(')) !== FALSE) 384 { 385 // Find closing bracket 386 $close = strpos($str, ')', $open) - 1; 387 388 // Find the type without the size 389 $type = substr($str, 0, $open); 390 } 391 else 392 { 393 // No length 394 $type = $str; 395 } 396 397 empty($sql_types[$type]) and exit 398 ( 399 'Unknown field type: '.$type.'. '. 400 'Please report this: http://trac.kohanaphp.com/newticket' 401 ); 402 403 // Fetch the field definition 404 $field = $sql_types[$type]; 405 406 switch ($field['type']) 407 { 408 case 'string': 409 case 'float': 410 if (isset($close)) 411 { 412 // Add the length to the field info 413 $field['length'] = substr($str, $open + 1, $close - $open); 414 } 415 break; 416 case 'int': 417 // Add unsigned value 418 $field['unsigned'] = (strpos($str, 'unsigned') !== FALSE); 419 break; 420 } 421 422 return $field; 423 } 424 425 /** 426 * Clears the internal query cache. 427 * 428 * @param string SQL query 429 */ 430 public function clear_cache($sql = NULL) 431 { 432 if (empty($sql)) 433 { 434 $this->query_cache = array(); 435 } 436 else 437 { 438 unset($this->query_cache[$this->query_hash($sql)]); 439 } 440 441 Kohana::log('debug', 'Database cache cleared: '.get_class($this)); 442 } 443 444 /** 445 * Creates a hash for an SQL query string. Replaces newlines with spaces, 446 * trims, and hashes. 447 * 448 * @param string SQL query 449 * @return string 450 */ 451 protected function query_hash($sql) 452 { 453 return sha1(str_replace("\n", ' ', trim($sql))); 454 } 455 456} // End Database Driver Interface 457 458/** 459 * Database_Result 460 * 461 */ 462abstract class Database_Result implements ArrayAccess, Iterator, Countable { 463 464 // Result resource, insert id, and SQL 465 protected $result; 466 protected $insert_id; 467 protected $sql; 468 469 // Current and total rows 470 protected $current_row = 0; 471 protected $total_rows = 0; 472 473 // Fetch function and return type 474 protected $fetch_type; 475 protected $return_type; 476 477 /** 478 * Returns the SQL used to fetch the result. 479 * 480 * @return string 481 */ 482 public function sql() 483 { 484 return $this->sql; 485 } 486 487 /** 488 * Returns the insert id from the result. 489 * 490 * @return mixed 491 */ 492 public function insert_id() 493 { 494 return $this->insert_id; 495 } 496 497 /** 498 * Prepares the query result. 499 * 500 * @param boolean return rows as objects 501 * @param mixed type 502 * @return Database_Result 503 */ 504 abstract function result($object = TRUE, $type = FALSE); 505 506 /** 507 * Builds an array of query results. 508 * 509 * @param boolean return rows as objects 510 * @param mixed type 511 * @return array 512 */ 513 abstract function result_array($object = NULL, $type = FALSE); 514 515 /** 516 * Gets the fields of an already run query. 517 * 518 * @return array 519 */ 520 abstract public function list_fields(); 521 522 /** 523 * Seek to an offset in the results. 524 * 525 * @return boolean 526 */ 527 abstract public function seek($offset); 528 529 /** 530 * Countable: count 531 */ 532 public function count() 533 { 534 return $this->total_rows; 535 } 536 537 /** 538 * ArrayAccess: offsetExists 539 */ 540 public function offsetExists($offset) 541 { 542 if ($this->total_rows > 0) 543 { 544 $min = 0; 545 $max = $this->total_rows - 1; 546 547 return ! ($offset < $min OR $offset > $max); 548 } 549 550 return FALSE; 551 } 552 553 /** 554 * ArrayAccess: offsetGet 555 */ 556 public function offsetGet($offset) 557 { 558 if ( ! $this->seek($offset)) 559 return FALSE; 560 561 // Return the row by calling the defined fetching callback 562 return call_user_func($this->fetch_type, $this->result, $this->return_type); 563 } 564 565 /** 566 * ArrayAccess: offsetSet 567 * 568 * @throws Kohana_Database_Exception 569 */ 570 final public function offsetSet($offset, $value) 571 { 572 throw new Kohana_Database_Exception('database.result_read_only'); 573 } 574 575 /** 576 * ArrayAccess: offsetUnset 577 * 578 * @throws Kohana_Database_Exception 579 */ 580 final public function offsetUnset($offset) 581 { 582 throw new Kohana_Database_Exception('database.result_read_only'); 583 } 584 585 /** 586 * Iterator: current 587 */ 588 public function current() 589 { 590 return $this->offsetGet($this->current_row); 591 } 592 593 /** 594 * Iterator: key 595 */ 596 public function key() 597 { 598 return $this->current_row; 599 } 600 601 /** 602 * Iterator: next 603 */ 604 public function next() 605 { 606 ++$this->current_row; 607 return $this; 608 } 609 610 /** 611 * Iterator: prev 612 */ 613 public function prev() 614 { 615 --$this->current_row; 616 return $this; 617 } 618 619 /** 620 * Iterator: rewind 621 */ 622 public function rewind() 623 { 624 $this->current_row = 0; 625 return $this; 626 } 627 628 /** 629 * Iterator: valid 630 */ 631 public function valid() 632 { 633 return $this->offsetExists($this->current_row); 634 } 635 636} // End Database Result Interface 637