1 <?php defined('SYSPATH') OR die('No direct access allowed.');
5 * $Id: Database.php 4343 2009-05-08 17:04:48Z jheathco $
9 * @copyright (c) 2007-2008 Kohana Team
10 * @license http://kohanaphp.com/license.html
12 abstract class Database_Driver {
14 protected $query_cache;
17 * Connect to our database.
18 * Returns FALSE on failure or a MySQL resource.
22 abstract public function connect();
25 * Perform a query based on a manually written query.
27 * @param string SQL query to execute
28 * @return Database_Result
30 abstract public function query($sql);
33 * Builds a DELETE query.
35 * @param string table name
36 * @param array where clause
39 public function delete($table, $where)
41 return 'DELETE FROM '.$this->escape_table($table).' WHERE '.implode(' ', $where);
45 * Builds an UPDATE query.
47 * @param string table name
48 * @param array key => value pairs
49 * @param array where clause
52 public function update($table, $values, $where)
54 foreach ($values as $key => $val)
56 $valstr[] = $this->escape_column($key).' = '.$val;
58 return 'UPDATE '.$this->escape_table($table).' SET '.implode(', ', $valstr).' WHERE '.implode(' ',$where);
62 * Set the charset using 'SET NAMES <charset>'.
64 * @param string character set to use
66 public function set_charset($charset)
68 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__);
72 * Wrap the tablename in backticks, has support for: table.field syntax.
74 * @param string table name
77 abstract public function escape_table($table);
80 * Escape a column/field name, has support for special commands.
82 * @param string column name
85 abstract public function escape_column($column);
88 * Builds a WHERE portion of a query.
93 * @param int number of where clauses
94 * @param boolean escape the value
97 public function where($key, $value, $type, $num_wheres, $quote)
99 $prefix = ($num_wheres == 0) ? '' : $type;
109 if ( ! $this->has_operator($key))
116 elseif (is_bool($value))
118 if ( ! $this->has_operator($key))
123 $value = ($value == TRUE) ? ' 1' : ' 0';
127 if ( ! $this->has_operator($key) AND ! empty($key))
129 $key = $this->escape_column($key).' =';
133 preg_match('/^(.+?)([<>!=]+|\bIS(?:\s+NULL))\s*$/i', $key, $matches);
134 if (isset($matches[1]) AND isset($matches[2]))
136 $key = $this->escape_column(trim($matches[1])).' '.trim($matches[2]);
140 $value = ' '.(($quote == TRUE) ? $this->escape($value) : $value);
144 return $prefix.$key.$value;
148 * Builds a LIKE portion of a query.
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
157 public function like($field, $match, $auto, $type, $num_likes)
159 $prefix = ($num_likes == 0) ? '' : $type;
161 $match = $this->escape_str($match);
165 // Add the start and end quotes
166 $match = '%'.str_replace('%', '\\%', $match).'%';
169 return $prefix.' '.$this->escape_column($field).' LIKE \''.$match . '\'';
173 * Builds a NOT LIKE portion of a query.
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
181 public function notlike($field, $match, $auto, $type, $num_likes)
183 $prefix = ($num_likes == 0) ? '' : $type;
185 $match = $this->escape_str($match);
189 // Add the start and end quotes
190 $match = '%'.$match.'%';
193 return $prefix.' '.$this->escape_column($field).' NOT LIKE \''.$match.'\'';
197 * Builds a REGEX portion of a query.
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
205 public function regex($field, $match, $type, $num_regexs)
207 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__);
211 * Builds a NOT REGEX portion of a query.
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
219 public function notregex($field, $match, $type, $num_regexs)
221 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__);
225 * Builds an INSERT query.
227 * @param string table name
229 * @param array values
232 public function insert($table, $keys, $values)
234 // Escape the column names
235 foreach ($keys as $key => $value)
237 $keys[$key] = $this->escape_column($value);
239 return 'INSERT INTO '.$this->escape_table($table).' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
243 * Builds a MERGE portion of a query.
245 * @param string table name
247 * @param array values
250 public function merge($table, $keys, $values)
252 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__);
256 * Builds a LIMIT portion of a query.
258 * @param integer limit
259 * @param integer offset
262 abstract public function limit($limit, $offset = 0);
265 * Creates a prepared statement.
267 * @param string SQL query
268 * @return Database_Stmt
270 public function stmt_prepare($sql = '')
272 throw new Kohana_Database_Exception('database.not_implemented', __FUNCTION__);
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.
280 * @param array select query values
283 abstract public function compile_select($database);
286 * Determines if the string has an arithmetic operator in it.
288 * @param string string to check
291 public function has_operator($str)
293 return (bool) preg_match('/[<>!=]|\sIS(?:\s+NOT\s+)?\b|BETWEEN/i', trim($str));
297 * Escapes any input value.
299 * @param mixed value to escape
302 public function escape($value)
304 if ( ! $this->db_config['escape'])
307 switch (gettype($value))
310 $value = '\''.$this->escape_str($value).'\'';
313 $value = (int) $value;
316 // Convert to non-locale aware float to prevent possible commas
317 $value = sprintf('%F', $value);
320 $value = ($value === NULL) ? 'NULL' : $value;
324 return (string) $value;
328 * Escapes a string for a query.
330 * @param mixed value to escape
333 abstract public function escape_str($str);
336 * Lists all tables in the database.
340 abstract public function list_tables();
343 * Lists all fields in a table.
345 * @param string table name
348 abstract function list_fields($table);
351 * Returns the last database error.
355 abstract public function show_error();
358 * Returns field data about a table.
360 * @param string table name
363 abstract public function field_data($table);
366 * Fetches SQL type information about a field, in a generic format.
368 * @param string field datatype
371 protected function sql_type($str)
375 if ($sql_types === NULL)
377 // Load SQL data types
378 $sql_types = Kohana::config('sql_types');
381 $str = strtolower(trim($str));
383 if (($open = strpos($str, '(')) !== FALSE)
385 // Find closing bracket
386 $close = strpos($str, ')', $open) - 1;
388 // Find the type without the size
389 $type = substr($str, 0, $open);
397 empty($sql_types[$type]) and exit
399 'Unknown field type: '.$type.'. '.
400 'Please report this: http://trac.kohanaphp.com/newticket'
403 // Fetch the field definition
404 $field = $sql_types[$type];
406 switch ($field['type'])
412 // Add the length to the field info
413 $field['length'] = substr($str, $open + 1, $close - $open);
417 // Add unsigned value
418 $field['unsigned'] = (strpos($str, 'unsigned') !== FALSE);
426 * Clears the internal query cache.
428 * @param string SQL query
430 public function clear_cache($sql = NULL)
434 $this->query_cache = array();
438 unset($this->query_cache[$this->query_hash($sql)]);
441 Kohana::log('debug', 'Database cache cleared: '.get_class($this));
445 * Creates a hash for an SQL query string. Replaces newlines with spaces,
448 * @param string SQL query
451 protected function query_hash($sql)
453 return sha1(str_replace("\n", ' ', trim($sql)));
456 } // End Database Driver Interface
462 abstract class Database_Result implements ArrayAccess, Iterator, Countable {
464 // Result resource, insert id, and SQL
466 protected $insert_id;
469 // Current and total rows
470 protected $current_row = 0;
471 protected $total_rows = 0;
473 // Fetch function and return type
474 protected $fetch_type;
475 protected $return_type;
478 * Returns the SQL used to fetch the result.
482 public function sql()
488 * Returns the insert id from the result.
492 public function insert_id()
494 return $this->insert_id;
498 * Prepares the query result.
500 * @param boolean return rows as objects
502 * @return Database_Result
504 abstract function result($object = TRUE, $type = FALSE);
507 * Builds an array of query results.
509 * @param boolean return rows as objects
513 abstract function result_array($object = NULL, $type = FALSE);
516 * Gets the fields of an already run query.
520 abstract public function list_fields();
523 * Seek to an offset in the results.
527 abstract public function seek($offset);
532 public function count()
534 return $this->total_rows;
538 * ArrayAccess: offsetExists
540 public function offsetExists($offset)
542 if ($this->total_rows > 0)
545 $max = $this->total_rows - 1;
547 return ! ($offset < $min OR $offset > $max);
554 * ArrayAccess: offsetGet
556 public function offsetGet($offset)
558 if ( ! $this->seek($offset))
561 // Return the row by calling the defined fetching callback
562 return call_user_func($this->fetch_type, $this->result, $this->return_type);
566 * ArrayAccess: offsetSet
568 * @throws Kohana_Database_Exception
570 final public function offsetSet($offset, $value)
572 throw new Kohana_Database_Exception('database.result_read_only');
576 * ArrayAccess: offsetUnset
578 * @throws Kohana_Database_Exception
580 final public function offsetUnset($offset)
582 throw new Kohana_Database_Exception('database.result_read_only');
588 public function current()
590 return $this->offsetGet($this->current_row);
596 public function key()
598 return $this->current_row;
604 public function next()
606 ++$this->current_row;
613 public function prev()
615 --$this->current_row;
622 public function rewind()
624 $this->current_row = 0;
631 public function valid()
633 return $this->offsetExists($this->current_row);
636 } // End Database Result Interface