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.
Class declared in MODPATH/database/classes/kohana/database.php on line 19.
integer 1
integer 2
integer 3
integer 4
string
$defaultdefault instance name
string(7) "default"
array
$instancesDatabase instances
array(0)
string
$last_querythe last query executed
$_config
$_connection
$_identifier
$_instanceDisconnect 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
.
void
final public function __destruct()
{
$this->disconnect();
}
Returns the database instance name.
echo (string) $db;
string
final public function __toString()
{
return $this->_instance;
}
Start a SQL transaction
// Start the transactions
$db->begin();
try {
DB::insert('users')->values($user1)...
DB::insert('users')->values($user2)...
// Insert successful commit the changes
$db->commit();
}
catch (Database_Exception $e)
{
// Insert failed. Rolling back changes...
$db->rollback();
}
string
$mode
= NULL - Transaction modeboolean
abstract public function begin($mode = NULL);
Commit the current transaction
// Commit the database changes
$db->commit();
boolean
abstract public function commit();
Connect to the database. This is called automatically when the first query is executed.
$db->connect();
void
abstract public function connect();
Count the number of records in a table.
// Get the total number of records in the "users" table
$count = $db->count_records('users');
mixed
$table
required - Table name string or array(query, alias)integer
public function count_records($table)
{
// Quote the table name
$table = $this->quote_table($table);
return $this->query(Database::SELECT, 'SELECT COUNT(*) AS total_row_count FROM '.$table, FALSE)
->get('total_row_count');
}
Returns a normalized array describing the SQL data type
$db->datatype('char');
string
$type
required - SQL data typearray
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();
}
Disconnect from the database. This is called automatically by Database::__destruct. Clears the database instance from Database::$instances.
$db->disconnect();
boolean
public function disconnect()
{
unset(Database::$instances[$this->_instance]);
return TRUE;
}
Sanitize a string by escaping characters that could cause an SQL injection attack.
$value = $db->escape('any string');
string
$value
required - Value to quotestring
abstract public function escape($value);
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);
string
$name
= NULL - Instance namearray
$config
= NULL - Configuration parametersDatabase
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];
}
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);
string
$table
required - Table to get columns fromstring
$like
= NULL - Column to search forboolean
$add_prefix
= bool TRUE - Whether to add the table prefix automatically or notarray
abstract public function list_columns($table, $like = NULL, $add_prefix = TRUE);
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%');
string
$like
= NULL - Table to search forarray
abstract public function list_tables($like = NULL);
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');
integer
$type
required - Database::SELECT, Database::INSERT, etcstring
$sql
required - SQL querymixed
$as_object
= bool FALSE - Result object class string, TRUE for stdClass, FALSE for assoc arrayarray
$params
= NULL - Object construct parameters for result classobject
- Database_Result for SELECT queriesarray
- List (insert id, row count) for INSERT queriesinteger
- Number of affected rows for all other queriesabstract public function query($type, $sql, $as_object = FALSE, array $params = NULL);
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.
mixed
$value
required - Any value to quotestring
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);
}
Quote a database column name and add the table prefix if needed.
$column = $db->quote_column($column);
You can also use SQL methods within identifiers.
// The value of "column" will be quoted
$column = $db->quote_column('COUNT("column")');
mixed
$column
required - Column name or array(column, alias)string
public function quote_column($column)
{
if (is_array($column))
{
list($column, $alias) = $column;
}
if ($column instanceof Database_Query)
{
// Create a sub-query
$column = '('.$column->compile($this).')';
}
elseif ($column instanceof Database_Expression)
{
// Use a raw expression
$column = $column->value();
}
else
{
// Convert to a string
$column = (string) $column;
if ($column === '*')
{
return $column;
}
elseif (strpos($column, '"') !== FALSE)
{
// Quote the column in FUNC("column") identifiers
$column = preg_replace('/"(.+?)"/e', '$this->quote_column("$1")', $column);
}
elseif (strpos($column, '.') !== FALSE)
{
$parts = explode('.', $column);
if ($prefix = $this->table_prefix())
{
// Get the offset of the table name, 2nd-to-last part
$offset = count($parts) - 2;
// Add the table prefix to the table name
$parts[$offset] = $prefix.$parts[$offset];
}
foreach ($parts as & $part)
{
if ($part !== '*')
{
// Quote each of the parts
$part = $this->_identifier.$part.$this->_identifier;
}
}
$column = implode('.', $parts);
}
else
{
$column = $this->_identifier.$column.$this->_identifier;
}
}
if (isset($alias))
{
$column .= ' AS '.$this->_identifier.$alias.$this->_identifier;
}
return $column;
}
Quote a database identifier
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.
mixed
$value
required - Any identifierstring
public function quote_identifier($value)
{
if (is_array($value))
{
list($value, $alias) = $value;
}
if ($value instanceof Database_Query)
{
// Create a sub-query
$value = '('.$value->compile($this).')';
}
elseif ($value instanceof Database_Expression)
{
// Use a raw expression
$value = $value->value();
}
else
{
// Convert to a string
$value = (string) $value;
if (strpos($value, '.') !== FALSE)
{
$parts = explode('.', $value);
foreach ($parts as & $part)
{
// Quote each of the parts
$part = $this->_identifier.$part.$this->_identifier;
}
$value = implode('.', $parts);
}
else
{
$value = $this->_identifier.$value.$this->_identifier;
}
}
if (isset($alias))
{
$value .= ' AS '.$this->_identifier.$alias.$this->_identifier;
}
return $value;
}
Quote a database table name and adds the table prefix if needed.
$table = $db->quote_table($table);
mixed
$table
required - Table name or array(table, alias)string
public function quote_table($table)
{
if (is_array($table))
{
list($table, $alias) = $table;
}
if ($table instanceof Database_Query)
{
// Create a sub-query
$table = '('.$table->compile($this).')';
}
elseif ($table instanceof Database_Expression)
{
// Use a raw expression
$table = $table->value();
}
else
{
// Convert to a string
$table = (string) $table;
if (strpos($table, '.') !== FALSE)
{
$parts = explode('.', $table);
if ($prefix = $this->table_prefix())
{
// Get the offset of the table name, last part
$offset = count($parts) - 1;
// Add the table prefix to the table name
$parts[$offset] = $prefix.$parts[$offset];
}
foreach ($parts as & $part)
{
// Quote each of the parts
$part = $this->_identifier.$part.$this->_identifier;
}
$table = implode('.', $parts);
}
else
{
// Add the table prefix
$table = $this->_identifier.$this->table_prefix().$table.$this->_identifier;
}
}
if (isset($alias))
{
// Attach table prefix to alias
$table .= ' AS '.$this->_identifier.$this->table_prefix().$alias.$this->_identifier;
}
return $table;
}
Abort the current transaction
// Undo the changes
$db->rollback();
boolean
abstract public function rollback();
Set the connection character set. This is called automatically by Database::connect.
$db->set_charset('utf8');
string
$charset
required - Character set namevoid
abstract public function set_charset($charset);
Return the table prefix defined in the current configuration.
$prefix = $db->table_prefix();
string
public function table_prefix()
{
return $this->_config['table_prefix'];
}
Stores the database configuration locally and name the instance.
This method cannot be accessed directly, you must use Database::instance.
void
protected function __construct($name, array $config)
{
// Set the instance name
$this->_instance = $name;
// Store the config locally
$this->_config = $config;
if (empty($this->_config['table_prefix']))
{
$this->_config['table_prefix'] = '';
}
// Store the database instance
Database::$instances[$name] = $this;
}
Extracts the text between parentheses, if any.
// Returns: array('CHAR', '6')
list($type, $length) = $db->_parse_type('CHAR(6)');
string
$type
required - $typearray
- List containing the type and length, if anyprotected function _parse_type($type)
{
if (($open = strpos($type, '(')) === FALSE)
{
// No length specified
return array($type, NULL);
}
// Closing parenthesis
$close = strrpos($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);
}