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/Database.php on line 3.
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
NULL
NULL
NULL
string(1) """NULL
Stores the database configuration locally and name the instance.
This method cannot be accessed directly, you must use Database::instance.
voidpublic 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'] = '';
}
}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.
voidpublic function __destruct()
{
$this->disconnect();
}Returns the database instance name.
echo (string) $db;
stringpublic 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 mode booleanabstract public function begin($mode = null);Commit the current transaction
// Commit the database changes
$db->commit();
booleanabstract public function commit();Connect to the database. This is called automatically when the first query is executed.
$db->connect();
voidabstract 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 [query, alias] integerpublic 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 type arraypublic function datatype($type)
{
static $types = [
// SQL-92
'bit' => ['type' => 'string', 'exact' => true],
'bit varying' => ['type' => 'string'],
'char' => ['type' => 'string', 'exact' => true],
'char varying' => ['type' => 'string'],
'character' => ['type' => 'string', 'exact' => true],
'character varying' => ['type' => 'string'],
'date' => ['type' => 'string'],
'dec' => ['type' => 'float', 'exact' => true],
'decimal' => ['type' => 'float', 'exact' => true],
'double precision' => ['type' => 'float'],
'float' => ['type' => 'float'],
'int' => ['type' => 'int', 'min' => '-2147483648', 'max' => '2147483647'],
'integer' => ['type' => 'int', 'min' => '-2147483648', 'max' => '2147483647'],
'interval' => ['type' => 'string'],
'national char' => ['type' => 'string', 'exact' => true],
'national char varying' => ['type' => 'string'],
'national character' => ['type' => 'string', 'exact' => true],
'national character varying' => ['type' => 'string'],
'nchar' => ['type' => 'string', 'exact' => true],
'nchar varying' => ['type' => 'string'],
'numeric' => ['type' => 'float', 'exact' => true],
'real' => ['type' => 'float'],
'smallint' => ['type' => 'int', 'min' => '-32768', 'max' => '32767'],
'time' => ['type' => 'string'],
'time with time zone' => ['type' => 'string'],
'timestamp' => ['type' => 'string'],
'timestamp with time zone' => ['type' => 'string'],
'varchar' => ['type' => 'string'],
// SQL:1999
'binary large object' => ['type' => 'string', 'binary' => true],
'blob' => ['type' => 'string', 'binary' => true],
'boolean' => ['type' => 'bool'],
'char large object' => ['type' => 'string'],
'character large object' => ['type' => 'string'],
'clob' => ['type' => 'string'],
'national character large object' => ['type' => 'string'],
'nchar large object' => ['type' => 'string'],
'nclob' => ['type' => 'string'],
'time without time zone' => ['type' => 'string'],
'timestamp without time zone' => ['type' => 'string'],
// SQL:2003
'bigint' => ['type' => 'int', 'min' => '-9223372036854775808', 'max' => '9223372036854775807'],
// SQL:2008
'binary' => ['type' => 'string', 'binary' => true, 'exact' => true],
'binary varying' => ['type' => 'string', 'binary' => true],
'varbinary' => ['type' => 'string', 'binary' => true],
];
if (isset($types[$type]))
return $types[$type];
return [];
}Disconnect from the database. This is called automatically by Database::__destruct. Clears the database instance from Database::$instances.
$db->disconnect();
booleanpublic 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 quote stringabstract 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 name array
$config
= NULL - Configuration parameters Databasepublic 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->load('database')->$name;
}
if (!isset($config['type'])) {
throw new Kohana_Exception('Database type not defined in :name configuration', [':name' => $name]);
}
// Set the driver class name
$driver = 'Database_' . ucfirst($config['type']);
// Create the database connection instance
$driver = new $driver($name, $config);
// Store the database instance
Database::$instances[$name] = $driver;
}
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 from string
$like
= NULL - Column to search for boolean
$add_prefix
= bool TRUE - Whether to add the table prefix automatically or not arrayabstract 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 for arrayabstract 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, 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 object - 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 be compiled.
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 quote stringpublic 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) {
// Compile the expression
return $value->compile($this);
} else {
// Convert the object to a string
return $this->quote((string) $value);
}
} elseif (is_array($value)) {
return '(' . implode(', ', array_map([$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.
$column = $db->quote_column(DB::expr('COUNT(`column`)'));
Objects passed to this function will be converted to strings.
Database_Expression objects will be compiled.
Database_Query objects will be compiled and converted to a sub-query.
All other objects will be converted using the __toString method.
mixed
$column
required - Column name or [column, alias] stringpublic function quote_column($column)
{
// Identifiers are escaped by repeating them
$escaped_identifier = $this->_identifier . $this->_identifier;
if (is_array($column)) {
list($column, $alias) = $column;
$alias = str_replace($this->_identifier, $escaped_identifier, $alias);
}
if ($column instanceof Database_Query) {
// Create a sub-query
$column = '(' . $column->compile($this) . ')';
} elseif ($column instanceof Database_Expression) {
// Compile the expression
$column = $column->compile($this);
} else {
// Convert to a string
$column = (string) $column;
$column = str_replace($this->_identifier, $escaped_identifier, $column);
if ($column === '*') {
return $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 be compiled.
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 identifier stringpublic function quote_identifier($value)
{
// Identifiers are escaped by repeating them
$escaped_identifier = $this->_identifier . $this->_identifier;
if (is_array($value)) {
list($value, $alias) = $value;
$alias = str_replace($this->_identifier, $escaped_identifier, $alias);
}
if ($value instanceof Database_Query) {
// Create a sub-query
$value = '(' . $value->compile($this) . ')';
} elseif ($value instanceof Database_Expression) {
// Compile the expression
$value = $value->compile($this);
} else {
// Convert to a string
$value = (string) $value;
$value = str_replace($this->_identifier, $escaped_identifier, $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);
Objects passed to this function will be converted to strings.
Database_Expression objects will be compiled.
Database_Query objects will be compiled and converted to a sub-query.
All other objects will be converted using the __toString method.
mixed
$table
required - Table name or [table, alias] stringpublic function quote_table($table)
{
// Identifiers are escaped by repeating them
$escaped_identifier = $this->_identifier . $this->_identifier;
if (is_array($table)) {
list($table, $alias) = $table;
$alias = str_replace($this->_identifier, $escaped_identifier, $alias);
}
if ($table instanceof Database_Query) {
// Create a sub-query
$table = '(' . $table->compile($this) . ')';
} elseif ($table instanceof Database_Expression) {
// Compile the expression
$table = $table->compile($this);
} else {
// Convert to a string
$table = (string) $table;
$table = str_replace($this->_identifier, $escaped_identifier, $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();
booleanabstract public function rollback();Set the connection character set. This is called automatically by Database::connect.
$db->set_charset('utf8');
string
$charset
required - Character set name voidabstract public function set_charset($charset);Return the table prefix defined in the current configuration.
$prefix = $db->table_prefix();
stringpublic function table_prefix()
{
return $this->_config['table_prefix'];
}Extracts the text between parentheses, if any.
// Returns: ['CHAR', '6']
list($type, $length) = $db->_parse_type('CHAR(6)');
string
$type
required - $type array - List containing the type and length, if anyprotected function _parse_type($type)
{
if (($open = strpos($type, '(')) === false) {
// No length specified
return [$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 [$type, $length];
}