Modules

abstract Database
extends Kohana_Database

Database connection wrapper/helper.

You may get a database instance using Database::instance('name') where name is the config group.

This class provides connection instance management via Database Drivers, as well as quoting, escaping and other related functions. Querys are done using Database_Query and Database_Query_Builder objects, which can be easily created using the DB helper class.

package: Kohana/Database
category: Base
author: Kohana Team
copyright: © 2008-2010 Kohana Team
license: http://kohanaphp.com/license

Class declared in MODPATH/database/classes/database.php on line 3.

Constants

SELECT

integer 1

INSERT

integer 2

UPDATE

integer 3

DELETE

integer 4

Properties

public static string $default

default instance name

string(7) "default"

public static array $instances

Database instances

array(0)

public string $last_query

the last query executed

protected $_config

protected $_connection

protected $_identifier

protected $_instance

Methods

final public __destruct( ) (defined in Kohana_Database)

Disconnect from the database when the object is destroyed.

// Destroy the database instance
unset(Database::instances[(string) $db], $db);

Calling unset($db) is not enough to destroy the database, as it will still be stored in Database::$instances.

Return Values

void

Source Code

final public function __destruct()
{
	$this->disconnect();
}

final public __toString( ) (defined in Kohana_Database)

Returns the database instance name.

echo (string) $db;

Return Values

string

Source Code

final public function __toString()
{
	return $this->_instance;
}

abstract public connect( ) (defined in Kohana_Database)

Connect to the database. This is called automatically when the first query is executed.

$db->connect();

Return Values

void

Source Code

abstract public function connect();

public count_last_query( ) (defined in Kohana_Database)

Count the number of records in the last query, without LIMIT or OFFSET applied.

// Get the total number of records that match the last query
$count = $db->count_last_query();

Return Values

integer

Source Code

public function count_last_query()
{
	if ($sql = $this->last_query)
	{
		$sql = trim($sql);
		if (stripos($sql, 'SELECT') !== 0)
		{
			return FALSE;
		}

		if (stripos($sql, 'LIMIT') !== FALSE)
		{
			// Remove LIMIT from the SQL
			$sql = preg_replace('/\sLIMIT\s+[^a-z]+/i', ' ', $sql);
		}

		if (stripos($sql, 'OFFSET') !== FALSE)
		{
			// Remove OFFSET from the SQL
			$sql = preg_replace('/\sOFFSET\s+\d+/i', '', $sql);
		}

		// Get the total rows from the last query executed
		$result = $this->query
		(
			Database::SELECT,
			'SELECT COUNT(*) AS '.$this->quote_identifier('total_rows').' '
			.'FROM ('.$sql.') AS '.$this->quote_table('counted_results'),
			TRUE
		);

		// Return the total number of rows from the query
		return (int) $result->current()->total_rows;
	}

	return FALSE;
}

public count_records( mixed $table ) (defined in Kohana_Database)

Count the number of records in a table.

// Get the total number of records in the "users" table
$count = $db->count_records('users');

Parameters

mixed $table required - Table name string or array(query, alias)

Return Values

integer

Source Code

public function count_records($table)
{
	// Quote the table name
	$table = $this->quote_identifier($table);

	return $this->query(Database::SELECT, 'SELECT COUNT(*) AS total_row_count FROM '.$table, FALSE)
		->get('total_row_count');
}

public datatype( string $type ) (defined in Kohana_Database)

Returns a normalized array describing the SQL data type

$db->datatype('char');

Parameters

string $type required - SQL data type

Return Values

array

Source Code

public function datatype($type)
{
	static $types = array
	(
		// SQL-92
		'bit'                           => array('type' => 'string', 'exact' => TRUE),
		'bit varying'                   => array('type' => 'string'),
		'char'                          => array('type' => 'string', 'exact' => TRUE),
		'char varying'                  => array('type' => 'string'),
		'character'                     => array('type' => 'string', 'exact' => TRUE),
		'character varying'             => array('type' => 'string'),
		'date'                          => array('type' => 'string'),
		'dec'                           => array('type' => 'float', 'exact' => TRUE),
		'decimal'                       => array('type' => 'float', 'exact' => TRUE),
		'double precision'              => array('type' => 'float'),
		'float'                         => array('type' => 'float'),
		'int'                           => array('type' => 'int', 'min' => '-2147483648', 'max' => '2147483647'),
		'integer'                       => array('type' => 'int', 'min' => '-2147483648', 'max' => '2147483647'),
		'interval'                      => array('type' => 'string'),
		'national char'                 => array('type' => 'string', 'exact' => TRUE),
		'national char varying'         => array('type' => 'string'),
		'national character'            => array('type' => 'string', 'exact' => TRUE),
		'national character varying'    => array('type' => 'string'),
		'nchar'                         => array('type' => 'string', 'exact' => TRUE),
		'nchar varying'                 => array('type' => 'string'),
		'numeric'                       => array('type' => 'float', 'exact' => TRUE),
		'real'                          => array('type' => 'float'),
		'smallint'                      => array('type' => 'int', 'min' => '-32768', 'max' => '32767'),
		'time'                          => array('type' => 'string'),
		'time with time zone'           => array('type' => 'string'),
		'timestamp'                     => array('type' => 'string'),
		'timestamp with time zone'      => array('type' => 'string'),
		'varchar'                       => array('type' => 'string'),

		// SQL:1999
		'binary large object'               => array('type' => 'string', 'binary' => TRUE),
		'blob'                              => array('type' => 'string', 'binary' => TRUE),
		'boolean'                           => array('type' => 'bool'),
		'char large object'                 => array('type' => 'string'),
		'character large object'            => array('type' => 'string'),
		'clob'                              => array('type' => 'string'),
		'national character large object'   => array('type' => 'string'),
		'nchar large object'                => array('type' => 'string'),
		'nclob'                             => array('type' => 'string'),
		'time without time zone'            => array('type' => 'string'),
		'timestamp without time zone'       => array('type' => 'string'),

		// SQL:2003
		'bigint'    => array('type' => 'int', 'min' => '-9223372036854775808', 'max' => '9223372036854775807'),

		// SQL:2008
		'binary'            => array('type' => 'string', 'binary' => TRUE, 'exact' => TRUE),
		'binary varying'    => array('type' => 'string', 'binary' => TRUE),
		'varbinary'         => array('type' => 'string', 'binary' => TRUE),
	);

	if (isset($types[$type]))
		return $types[$type];

	return array();
}

public disconnect( ) (defined in Kohana_Database)

Disconnect from the database. This is called automatically by Database::__destruct. Clears the database instance from Database::$instances.

$db->disconnect();

Return Values

boolean

Source Code

public function disconnect()
{
	unset(Database::$instances[$this->_instance]);

	return TRUE;
}

abstract public escape( string $value ) (defined in Kohana_Database)

Sanitize a string by escaping characters that could cause an SQL injection attack.

$value = $db->escape('any string');

Parameters

string $value required - Value to quote

Return Values

string

Source Code

abstract public function escape($value);

public static instance( [ string $name = NULL , array $config = NULL ] ) (defined in Kohana_Database)

Get a singleton Database instance. If configuration is not specified, it will be loaded from the database configuration file using the same group as the name.

// Load the default database
$db = Database::instance();

// Create a custom configured instance
$db = Database::instance('custom', $config);

Parameters

string $name = NULL - Instance name
array $config = NULL - Configuration parameters

Return Values

Database

Source Code

public static function instance($name = NULL, array $config = NULL)
{
	if ($name === NULL)
	{
		// Use the default instance name
		$name = Database::$default;
	}

	if ( ! isset(Database::$instances[$name]))
	{
		if ($config === NULL)
		{
			// Load the configuration for this database
			$config = Kohana::config('database')->$name;
		}

		if ( ! isset($config['type']))
		{
			throw new Kohana_Exception('Database type not defined in :name configuration',
				array(':name' => $name));
		}

		// Set the driver class name
		$driver = 'Database_'.ucfirst($config['type']);

		// Create the database connection instance
		new $driver($name, $config);
	}

	return Database::$instances[$name];
}

abstract public list_columns( string $table [, string $like = NULL , boolean $add_prefix = bool TRUE ] ) (defined in Kohana_Database)

Lists all of the columns in a table. Optionally, a LIKE string can be used to search for specific fields.

// Get all columns from the "users" table
$columns = $db->list_columns('users');

// Get all name-related columns
$columns = $db->list_columns('users', '%name%');

// Get the columns from a table that doesn't use the table prefix
$columns = $db->list_columns('users', NULL, FALSE);

Parameters

string $table required - Table to get columns from
string $like = NULL - Column to search for
boolean $add_prefix = bool TRUE - Whether to add the table prefix automatically or not

Return Values

array

Source Code

abstract public function list_columns($table, $like = NULL, $add_prefix = TRUE);

abstract public list_tables( [ string $like = NULL ] ) (defined in Kohana_Database)

List all of the tables in the database. Optionally, a LIKE string can be used to search for specific tables.

// Get all tables in the current database
$tables = $db->list_tables();

// Get all user-related tables
$tables = $db->list_tables('user%');

Parameters

string $like = NULL - Table to search for

Return Values

array

Source Code

abstract public function list_tables($like = NULL);

abstract public query( integer $type , string $sql [, mixed $as_object = bool FALSE , array $params = NULL ] ) (defined in Kohana_Database)

Perform an SQL query of the given type.

// Make a SELECT query and use objects for results
$db->query(Database::SELECT, 'SELECT * FROM groups', TRUE);

// Make a SELECT query and use "Model_User" for the results
$db->query(Database::SELECT, 'SELECT * FROM users LIMIT 1', 'Model_User');

Parameters

integer $type required - Database::SELECT, Database::INSERT, etc
string $sql required - SQL query
mixed $as_object = bool FALSE - Result object class string, TRUE for stdClass, FALSE for assoc array
array $params = NULL - Object construct parameters for result class

Return Values

object - Database_Result for SELECT queries
array - List (insert id, row count) for INSERT queries
integer - Number of affected rows for all other queries

Source Code

abstract public function query($type, $sql, $as_object = FALSE, array $params = NULL);

public quote( mixed $value ) (defined in Kohana_Database)

Quote a value for an SQL query.

$db->quote(NULL);   // 'NULL'
$db->quote(10);     // 10
$db->quote('fred'); // 'fred'

Objects passed to this function will be converted to strings. Database_Expression objects will use the value of the expression. Database_Query objects will be compiled and converted to a sub-query. All other objects will be converted using the __toString method.

Parameters

mixed $value required - Any value to quote

Return Values

string

Source Code

public function quote($value)
{
	if ($value === NULL)
	{
		return 'NULL';
	}
	elseif ($value === TRUE)
	{
		return "'1'";
	}
	elseif ($value === FALSE)
	{
		return "'0'";
	}
	elseif (is_object($value))
	{
		if ($value instanceof Database_Query)
		{
			// Create a sub-query
			return '('.$value->compile($this).')';
		}
		elseif ($value instanceof Database_Expression)
		{
			// Use a raw expression
			return $value->value();
		}
		else
		{
			// Convert the object to a string
			return $this->quote( (string) $value);
		}
	}
	elseif (is_array($value))
	{
		return '('.implode(', ', array_map(array($this, __FUNCTION__), $value)).')';
	}
	elseif (is_int($value))
	{
		return (int) $value;
	}
	elseif (is_float($value))
	{
		// Convert to non-locale aware float to prevent possible commas
		return sprintf('%F', $value);
	}

	return $this->escape($value);
}

public quote_identifier( mixed $value ) (defined in Kohana_Database)

Quote a database identifier, such as a column name. Adds the table prefix to the identifier if a table name is present.

$column = $db->quote_identifier($column);

You can also use SQL methods within identifiers.

// The value of "column" will be quoted
$column = $db->quote_identifier('COUNT("column")');

Parameters

mixed $value required - Any identifier

Return Values

string

Source Code

public function quote_identifier($value)
{
	if ($value === '*')
	{
		return $value;
	}
	elseif (is_object($value))
	{
		if ($value instanceof Database_Query)
		{
			// Create a sub-query
			return '('.$value->compile($this).')';
		}
		elseif ($value instanceof Database_Expression)
		{
			// Use a raw expression
			return $value->value();
		}
		else
		{
			// Convert the object to a string
			return $this->quote_identifier( (string) $value);
		}
	}
	elseif (is_array($value))
	{
		// Separate the column and alias
		list ($value, $alias) = $value;

		return $this->quote_identifier($value).' AS '.$this->quote_identifier($alias);
	}

	if (strpos($value, '"') !== FALSE)
	{
		// Quote the column in FUNC("ident") identifiers
		return preg_replace('/"(.+?)"/e', '$this->quote_identifier("$1")', $value);
	}
	elseif (strpos($value, '.') !== FALSE)
	{
		// Split the identifier into the individual parts
		$parts = explode('.', $value);

		if ($prefix = $this->table_prefix())
		{
			// Get the offset of the table name, 2nd-to-last part
			// This works for databases that can have 3 identifiers (Postgre)
			$offset = count($parts) - 2;

			// Add the table prefix to the table name
			$parts[$offset] = $prefix.$parts[$offset];
		}

		// Quote each of the parts
		return implode('.', array_map(array($this, __FUNCTION__), $parts));
	}
	else
	{
		return $this->_identifier.$value.$this->_identifier;
	}
}

public quote_table( mixed $value ) (defined in Kohana_Database)

Quote a database table name and adds the table prefix if needed.

$table = $db->quote_table($table);

Parameters

mixed $value required - Table name or array(table, alias)

Return Values

string

Source Code

public function quote_table($value)
{
	// Assign the table by reference from the value
	if (is_array($value))
	{
		$table =& $value[0];

		// Attach table prefix to alias
		$value[1] = $this->table_prefix().$value[1];
	}
	else
	{
		$table =& $value;
	}

	if (is_string($table) AND strpos($table, '.') === FALSE)
	{
		// Add the table prefix for tables
		$table = $this->table_prefix().$table;
	}

	return $this->quote_identifier($value);
}

abstract public set_charset( string $charset ) (defined in Kohana_Database)

Set the connection character set. This is called automatically by Database::connect.

$db->set_charset('utf8');

Parameters

string $charset required - Character set name

Return Values

void

Source Code

abstract public function set_charset($charset);

public table_prefix( ) (defined in Kohana_Database)

Return the table prefix defined in the current configuration.

$prefix = $db->table_prefix();

Return Values

string

Source Code

public function table_prefix()
{
	return $this->_config['table_prefix'];
}

protected __construct( ) (defined in Kohana_Database)

Stores the database configuration locally and name the instance.

This method cannot be accessed directly, you must use Database::instance.

Return Values

void

Source Code

protected function __construct($name, array $config)
{
	// Set the instance name
	$this->_instance = $name;

	// Store the config locally
	$this->_config = $config;

	// Store the database instance
	Database::$instances[$name] = $this;
}

protected _parse_type( string $type ) (defined in Kohana_Database)

Extracts the text between parentheses, if any.

// Returns: array('CHAR', '6')
list($type, $length) = $db->_parse_type('CHAR(6)');

Parameters

string $type required

Return Values

array - List containing the type and length, if any

Source Code

protected function _parse_type($type)
{
	if (($open = strpos($type, '(')) === FALSE)
	{
		// No length specified
		return array($type, NULL);
	}

	// Closing parenthesis
	$close = strpos($type, ')', $open);

	// Length without parentheses
	$length = substr($type, $open + 1, $close - 1 - $open);

	// Type without the length
	$type = substr($type, 0, $open).substr($type, $close + 1);

	return array($type, $length);
}

Modules

abstract Database extends Kohana_Database

Constants

Properties

Methods

Constants

SELECT

INSERT

UPDATE

DELETE

Properties

public static string $default

public static array $instances

public string $last_query

protected $_config

protected $_connection

protected $_identifier

protected $_instance

Methods

final public __destruct( ) (defined in Kohana_Database)

Return Values

Source Code

final public __toString( ) (defined in Kohana_Database)

Return Values

Source Code

abstract public connect( ) (defined in Kohana_Database)

Tags

Return Values

Source Code

public count_last_query( ) (defined in Kohana_Database)

Tags

Return Values

Source Code

public count_records( mixed $table ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

public datatype( string $type ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

public disconnect( ) (defined in Kohana_Database)

Return Values

Source Code

abstract public escape( string $value ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

public static instance( [ string $name = NULL , array $config = NULL ] ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

abstract public list_columns( string $table [, string $like = NULL , boolean $add_prefix = bool TRUE ] ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

abstract public list_tables( [ string $like = NULL ] ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

abstract public query( integer $type , string $sql [, mixed $as_object = bool FALSE , array $params = NULL ] ) (defined in Kohana_Database)

Parameters

Return Values

Source Code

public quote( mixed $value ) (defined in Kohana_Database)

Parameters

Tags

Return Values

Source Code

public quote_identifier( mixed $value ) (defined in Kohana_Database)

Parameters

Tags

Return Values

Source Code

public quote_table( mixed $value ) (defined in Kohana_Database)

Parameters

Tags

Return Values

Source Code

abstract public set_charset( string $charset ) (defined in Kohana_Database)

abstract Database
extends Kohana_Database

public static `string` $default

public static `array` $instances

public `string` $last_query