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