Modules
Database_MySQL
extends Kohana_Database_MySQL
extends Database
extends Kohana_Database
MySQL database connection.
Class declared in MODPATH/database/classes/database/mysql.php on line 3.
Properties
Constants
SELECT
integer 1
INSERT
integer 2
UPDATE
integer 3
DELETE
integer 4
Properties
public static
string$defaultdefault instance name
string(7) "default"public static
array$instancesDatabase instances
array(0)public
string$last_querythe last query executed
protected
$_configprotected
$_connectionprotected
$_connection_idprotected static
$_current_databasesarray(0)protected
$_identifierprotected
$_instanceprotected static
$_set_namesNULL
Methods
public connect( ) (defined in Kohana_Database_MySQL)
Connect to the database. This is called automatically when the first query is executed.
$db->connect();
Tags
Return Values
void
Source Code
public function connect()
{
if ($this->_connection)
return;
if (Database_MySQL::$_set_names === NULL)
{
// Determine if we can use mysql_set_charset(), which is only
// available on PHP 5.2.3+ when compiled against MySQL 5.0+
Database_MySQL::$_set_names = ! function_exists('mysql_set_charset');
}
// Extract the connection parameters, adding required variabels
extract($this->_config['connection'] + array(
'database' => '',
'hostname' => '',
'username' => '',
'password' => '',
'persistent' => FALSE,
));
// Prevent this information from showing up in traces
unset($this->_config['connection']['username'], $this->_config['connection']['password']);
try
{
if ($persistent)
{
// Create a persistent connection
$this->_connection = mysql_pconnect($hostname, $username, $password);
}
else
{
// Create a connection and force it to be a new link
$this->_connection = mysql_connect($hostname, $username, $password, TRUE);
}
}
catch (ErrorException $e)
{
// No connection exists
$this->_connection = NULL;
throw new Database_Exception(mysql_errno(), '[:code] :error', array(
':code' => mysql_errno(),
':error' => mysql_error(),
));
}
// \xFF is a better delimiter, but the PHP driver uses underscore
$this->_connection_id = sha1($hostname.'_'.$username.'_'.$password);
$this->_select_db($database);
if ( ! empty($this->_config['charset']))
{
// Set the character set
$this->set_charset($this->_config['charset']);
}
}
public datatype( string $type ) (defined in Kohana_Database_MySQL)
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
(
'blob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '65535'),
'bool' => array('type' => 'bool'),
'bigint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '18446744073709551615'),
'datetime' => array('type' => 'string'),
'decimal unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'),
'double' => array('type' => 'float'),
'double precision unsigned' => array('type' => 'float', 'min' => '0'),
'double unsigned' => array('type' => 'float', 'min' => '0'),
'enum' => array('type' => 'string'),
'fixed' => array('type' => 'float', 'exact' => TRUE),
'fixed unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'),
'float unsigned' => array('type' => 'float', 'min' => '0'),
'int unsigned' => array('type' => 'int', 'min' => '0', 'max' => '4294967295'),
'integer unsigned' => array('type' => 'int', 'min' => '0', 'max' => '4294967295'),
'longblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '4294967295'),
'longtext' => array('type' => 'string', 'character_maximum_length' => '4294967295'),
'mediumblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '16777215'),
'mediumint' => array('type' => 'int', 'min' => '-8388608', 'max' => '8388607'),
'mediumint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '16777215'),
'mediumtext' => array('type' => 'string', 'character_maximum_length' => '16777215'),
'national varchar' => array('type' => 'string'),
'numeric unsigned' => array('type' => 'float', 'exact' => TRUE, 'min' => '0'),
'nvarchar' => array('type' => 'string'),
'point' => array('type' => 'string', 'binary' => TRUE),
'real unsigned' => array('type' => 'float', 'min' => '0'),
'set' => array('type' => 'string'),
'smallint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '65535'),
'text' => array('type' => 'string', 'character_maximum_length' => '65535'),
'tinyblob' => array('type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '255'),
'tinyint' => array('type' => 'int', 'min' => '-128', 'max' => '127'),
'tinyint unsigned' => array('type' => 'int', 'min' => '0', 'max' => '255'),
'tinytext' => array('type' => 'string', 'character_maximum_length' => '255'),
'year' => array('type' => 'string'),
);
$type = str_replace(' zerofill', '', $type);
if (isset($types[$type]))
return $types[$type];
return parent::datatype($type);
}
public disconnect( ) (defined in Kohana_Database_MySQL)
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()
{
try
{
// Database is assumed disconnected
$status = TRUE;
if (is_resource($this->_connection))
{
if ($status = mysql_close($this->_connection))
{
// Clear the connection
$this->_connection = NULL;
// Clear the instance
parent::disconnect();
}
}
}
catch (Exception $e)
{
// Database is probably not disconnected
$status = ! is_resource($this->_connection);
}
return $status;
}
public escape( string $value ) (defined in Kohana_Database_MySQL)
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
public function escape($value)
{
// Make sure the database is connected
$this->_connection or $this->connect();
if (($value = mysql_real_escape_string( (string) $value, $this->_connection)) === FALSE)
{
throw new Database_Exception(mysql_errno($this->_connection), '[:code] :error', array(
':code' => mysql_errno($this->_connection),
':error' => mysql_error($this->_connection),
));
}
// SQL standard is to use single-quotes for all values
return "'$value'";
}
public list_columns( string $table [, string $like = NULL , boolean $add_prefix = bool TRUE ] ) (defined in Kohana_Database_MySQL)
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
public function list_columns($table, $like = NULL, $add_prefix = TRUE)
{
// Quote the table name
$table = ($add_prefix === TRUE) ? $this->quote_table($table) : $table;
if (is_string($like))
{
// Search for column names
$result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table.' LIKE '.$this->quote($like), FALSE);
}
else
{
// Find all column names
$result = $this->query(Database::SELECT, 'SHOW FULL COLUMNS FROM '.$table, FALSE);
}
$count = 0;
$columns = array();
foreach ($result as $row)
{
list($type, $length) = $this->_parse_type($row['Type']);
$column = $this->datatype($type);
$column['column_name'] = $row['Field'];
$column['column_default'] = $row['Default'];
$column['data_type'] = $type;
$column['is_nullable'] = ($row['Null'] == 'YES');
$column['ordinal_position'] = ++$count;
switch ($column['type'])
{
case 'float':
if (isset($length))
{
list($column['numeric_precision'], $column['numeric_scale']) = explode(',', $length);
}
break;
case 'int':
if (isset($length))
{
// MySQL attribute
$column['display'] = $length;
}
break;
case 'string':
switch ($column['data_type'])
{
case 'binary':
case 'varbinary':
$column['character_maximum_length'] = $length;
break;
case 'char':
case 'varchar':
$column['character_maximum_length'] = $length;
case 'text':
case 'tinytext':
case 'mediumtext':
case 'longtext':
$column['collation_name'] = $row['Collation'];
break;
case 'enum':
case 'set':
$column['collation_name'] = $row['Collation'];
$column['options'] = explode('\',\'', substr($length, 1, -1));
break;
}
break;
}
// MySQL attributes
$column['comment'] = $row['Comment'];
$column['extra'] = $row['Extra'];
$column['key'] = $row['Key'];
$column['privileges'] = $row['Privileges'];
$columns[$row['Field']] = $column;
}
return $columns;
}
public list_tables( [ string $like = NULL ] ) (defined in Kohana_Database_MySQL)
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
public function list_tables($like = NULL)
{
if (is_string($like))
{
// Search for table names
$result = $this->query(Database::SELECT, 'SHOW TABLES LIKE '.$this->quote($like), FALSE);
}
else
{
// Find all table names
$result = $this->query(Database::SELECT, 'SHOW TABLES', FALSE);
}
$tables = array();
foreach ($result as $row)
{
$tables[] = reset($row);
}
return $tables;
}
public query( integer $type , string $sql [, mixed $as_object = bool FALSE , array $params = NULL ] ) (defined in Kohana_Database_MySQL)
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 queriesarray- List (insert id, row count) for INSERT queriesinteger- Number of affected rows for all other queries
Source Code
public function query($type, $sql, $as_object = FALSE, array $params = NULL)
{
// Make sure the database is connected
$this->_connection or $this->connect();
if ( ! empty($this->_config['profiling']))
{
// Benchmark this query for the current instance
$benchmark = Profiler::start("Database ({$this->_instance})", $sql);
}
if ( ! empty($this->_config['connection']['persistent']) AND $this->_config['connection']['database'] !== Database_MySQL::$_current_databases[$this->_connection_id])
{
// Select database on persistent connections
$this->_select_db($this->_config['connection']['database']);
}
// Execute the query
if (($result = mysql_query($sql, $this->_connection)) === FALSE)
{
if (isset($benchmark))
{
// This benchmark is worthless
Profiler::delete($benchmark);
}
throw new Database_Exception(mysql_errno($this->_connection), '[:code] :error ( :query )', array(
':code' => mysql_errno($this->_connection),
':error' => mysql_error($this->_connection),
':query' => $sql,
));
}
if (isset($benchmark))
{
Profiler::stop($benchmark);
}
// Set the last query
$this->last_query = $sql;
if ($type === Database::SELECT)
{
// Return an iterator of results
return new Database_MySQL_Result($result, $sql, $as_object, $params);
}
elseif ($type === Database::INSERT)
{
// Return a list of insert id and rows created
return array(
mysql_insert_id($this->_connection),
mysql_affected_rows($this->_connection),
);
}
else
{
// Return the number of rows affected
return mysql_affected_rows($this->_connection);
}
}
public set_charset( string $charset ) (defined in Kohana_Database_MySQL)
Set the connection character set. This is called automatically by Database::connect.
$db->set_charset('utf8');
Parameters
-
string$charset required - Character set name
Tags
Return Values
void
Source Code
public function set_charset($charset)
{
// Make sure the database is connected
$this->_connection or $this->connect();
if (Database_MySQL::$_set_names === TRUE)
{
// PHP is compiled against MySQL 4.x
$status = (bool) mysql_query('SET NAMES '.$this->quote($charset), $this->_connection);
}
else
{
// PHP is compiled against MySQL 5.x
$status = mysql_set_charset($charset, $this->_connection);
}
if ($status === FALSE)
{
throw new Database_Exception(mysql_errno($this->_connection), '[:code] :error', array(
':code' => mysql_errno($this->_connection),
':error' => mysql_error($this->_connection),
));
}
}
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;
}
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();
Tags
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 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];
}
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
Tags
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")');
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 identifier
Tags
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)
Tags
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);
}
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 _select_db( string $database ) (defined in Kohana_Database_MySQL)
Select the database
Parameters
-
string$database required - Database
Return Values
void
Source Code
protected function _select_db($database)
{
if ( ! mysql_select_db($database, $this->_connection))
{
// Unable to select database
throw new Database_Exception(mysql_errno($this->_connection), '[:code] :error', array(
':code' => mysql_errno($this->_connection),
':error' => mysql_error($this->_connection),
));
}
Database_MySQL::$_current_databases[$this->_connection_id] = $database;
}
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);
}